以下是存儲過程，我平時用的
USE [db01]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create PROCEDURE [dbo].[P_公共_單列值轉多行]
@tb_from nvarchar(255), --源表
@tb_to nvarchar(255), --臨時存放表，臨時表和源表都要有以下兩個字段：主字段，分列段
@zhu_lie nvarchar(255), --主字段名，如ID，
@feng_lie nvarchar(255) --要分列的列名，如“姓名”
AS
BEGIN
SET NOCOUNT ON;
declare @zhu_value nvarchar(255) --主列數值
declare @feng_value nvarchar(500) --分列數值
declare @sql nvarchar(500) --sql語句
declare @weizhi1 as integer --記錄第一個分号位置
declare @weizhi2 as integer --記錄第二個分号位置
exec ('declare mycursor cursor for (select ' + @zhu_lie + ', '+ @feng_lie +' from ' + @tb_from + ')')
open mycursor
fetch next from mycursor into @zhu_value,@feng_value
while @@fetch_status = 0
begin
set @weizhi1 = 1
set @weizhi2 = 1
while @weizhi2 > 0
begin
set @weizhi2 = charindex(';',@feng_value,@weizhi1)
set @sql = ''
if @weizhi2 =0
begin
set @sql = 'insert into '[email protected]_to+'('[email protected]_lie+','[email protected]_lie +') values ('''[email protected]_value+''' ,'''+substring(@feng_value,@weizhi1,len(@feng_value))+''')'
exec(@sql)
end
else
begin
set @sql = 'insert into '[email protected]_to+'('[email protected]_lie+','[email protected]_lie +') values ('''[email protected]_value+''' ,'''+substring(@feng_value,@weizhi1,@[email protected])+''')'
exec(@sql)
set @weizhi1= @weizhi2 + 1
end
end
fetch next from mycursor into @zhu_value,@feng_value
end
close mycursor
deallocate mycursor
END