单列值转多行
emcs
36次下载
639次浏览
2019-08-14
4

脚本内容

alter 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

评论

贡献排行榜