用事务实现行转列

if object_id('Tempdb..#Roy') is not null
	drop table #Roy
create table  #Roy (a int,b varchar(10),c int,d int)
insert #Roy
select 1,    '小李', 1,    2 union all
select 2,    '小王', 4,    5  union all 
select 1,    '小李', 3,    4 union all
select 2,    '小王', 6,    7 union all
select 1,    '小李', 5,    6 union all
select 2,    '小王', 8,    9


sql2000实现:
begin tran 
	if object_id('tempdb..#roy2') is not null
		drop table #roy2
	select a,b,num,id=identity(int,1,1) 
	into #roy2			--生成临时表
	from (select a,b,c as num from #Roy
	union all
	select a,b,d from #Roy)a order by b,num asc		--排序方式

	--动态查询
	declare @s nvarchar(1000),@i int
	select top 1 @i=count(1),@s='' from #roy2 group by a order by count(1) desc
	while @i>0
		select @s=',[Col'+rtrim(@i)+']=max(case when ID='+rtrim(@i)+' then rtrim(num) else '''' end)'+@s,@i=@i-1
	exec('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 #roy2
rollback  tran

sql2005:

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) desc
	while @i>0
		select @s=','+quotename(@i)+@s,@Col=',[Col'+rtrim(@i)+']='+quotename(@i)+@Col,@i=@i-1
	set @s=stuff(@s,1,1,'')

	exec('with CTE
	as
	(select a,b,num,row=row_number()over(partition by a order by num)
	from 
		#roy
	unpivot
		(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           6
2           小王         4           5           6           7           8           9

(2 行受影响)
*/

原文链接： http://blog.csdn.net/roy_88/article/details/1509357

转载于:https://my.oschina.net/chen106106/blog/47509