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 '+@tb_to+'('+@zhu_lie+','+@feng_lie +') values ('''+@zhu_value+''' ,'''+substring(@feng_value,@weizhi1,len(@feng_value))+''')'
exec(@sql)
end
else
begin
set @sql = 'insert into '+@tb_to+'('+@zhu_lie+','+@feng_lie +') values ('''+@zhu_value+''' ,'''+substring(@feng_value,@weizhi1,@weizhi2-@weizhi1)+''')'
exec(@sql)
set @weizhi1= @weizhi2 + 1
end
end
fetch next from mycursor into @zhu_value,@feng_value
end
close mycursor
deallocate mycursor
END
