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 行受影响)
*/
