行列转换

Question

if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([apply_user_no] varchar(5),[stationery_id] int,[apply_number] int)
insert [tb]
select '00010',1001,100 union all
select '00010',1002,200 union all
select '00010',1003,300
go
--select * from [tb]declare @sql nvarchar(max)
set @sql='select apply_user_no'
select @sql=@sql+',stationery_id'+rtrim(rn)+'='+rtrim(stationery_id)+',apply_number'+rtrim(rn)+'=sum(case stationery_id when '+rtrim(stationery_id)+' then apply_number else 0 end)'
from (select rn=row_number() over(order by stationery_id),stationery_id from (select distinct stationery_id from tb) a) bexec(@sql+' from tb group by apply_user_no')--测试结果:
/*
apply_user_no stationery_id1 apply_number1 stationery_id2 apply_number2 stationery_id3 apply_number3
------------- -------------- ------------- -------------- ------------- -------------- -------------
00010         1001           100           1002           200           1003           300
*/