用事務實現行轉列

if object_id('Tempdb..#Roy') is not nulldrop table #Roycreate table  #Roy (a int,b varchar(10),c int,d int)insert #Royselect 1,    '小李', 1,    2 union allselect 2,    '小王', 4,    5  union all select 1,    '小李', 3,    4 union allselect 2,    '小王', 6,    7 union allselect 1,    '小李', 5,    6 union allselect 2,    '小王', 8,    9sql2000實現:begin tran if object_id('tempdb..#roy2') is not nulldrop table #roy2select a,b,num,id=identity(int,1,1) into #roy2--產生暫存資料表from (select a,b,c as num from #Royunion allselect a,b,d from #Roy)a order by b,num asc--排序方式--動態查詢declare @s nvarchar(1000),@i intselect top 1 @i=count(1),@s='' from #roy2 group by a order by count(1) descwhile @i>0select @s=',[Col'+rtrim(@i)+']=max(case when ID='+rtrim(@i)+' then rtrim(num) else '''' end)'+@s,@i=@i-1exec('select a,b'+@s+ 'from (select a,b,num,ID=(select count(1) from #roy2 where a=a.a and ID<=a.ID) from #roy2 a)T group by a,b')drop table #roy2rollback  transql2005:begin tran--開始事務declare @i int,@s nvarchar(1000),@Col nvarchar(1000)select top 1 @i=count(1),@s='',@Col=''  from #roy unpivot (Num for Col in(c,d))b  group by a order by count(1) descwhile @i>0select @s=','+quotename(@i)+@s,@Col=',[Col'+rtrim(@i)+']='+quotename(@i)+@Col,@i=@i-1set @s=stuff(@s,1,1,'')exec('with CTEas(select a,b,num,row=row_number()over(partition by a order by num)from #royunpivot(Num for Col in(c,d))b)select a,b'+@Col+' from CTE pivot (max(num) for row in('+@s+'))b')rollback tran/*a           b          Col1        Col2        Col3        Col4        Col5        Col6----------- ---------- ----------- ----------- ----------- ----------- ----------- -----------1           小李         1           2           3           4           5           62           小王         4           5           6           7           8           9(2 行受影響)*/