22,206
社区成员
发帖
与我相关
我的任务
分享
一个表数据如下:
ID Price
1 30
2 49
4 12
.....
想得到如果下数据:
1 2 3 4
30 49 NULL 12
如果没数据自动填充NULL值的行转列怎么写,应该要写成动态吧。
declare @s varchar(8000)
select @s=isnull(@s+',','')+'['+cast([id] as varchar)+']=max(case when id='+cast([id] as varchar)+' then Price else NULL end)'
from(select number as id from master..spt_values where type = 'P' and number >0 and number <=(select max(id) from ta))a
exec('select '+ @s+' from ta ')
go
create table tb(id int,price int)
insert into tb
select 1,10 union select 2,20 union
select 3,70 union select 9,30 union
select 5,60 union select 8,40
declare @sql varchar(8000),@max int,@min int
select @max=MAX(id),@min=MIN(id) from tb
set @sql = ''
select @sql = @sql + ',max(case id when ' + rtrim(number) + ' then price end) [' + rtrim(number) + ']'
from master..spt_values where number between @min and @max and type='p'
set @sql = 'select '+stuff(@sql,1,1,'') + ' from tb '
exec(@sql)
/*
1 2 3 4 5 6 7 8 9
----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
10 20 70 NULL 60 NULL NULL 40 30
警告: 聚合或其他 SET 操作消除了 Null 值。
(1 行受影响)