有表结构如下
SO CostGroup CostAmount
1 A 100
1 B 200
2 B 300
2 C 400
3 A 500
3 B 600
3 D 700
要查询出如下格式:
SO CostGroupA CostGroupB CostGroupC CostGroupD
1 100 200 0 0
2 0 300 400 0
3 500 600 0 700
------解决方案--------------------
create table T(SO int, CostGroup varchar(10), CostAmount int)
insert T select 1, 'A ', 100
union all select 1, 'B ', 200
union all select 2, 'B ', 300
union all select 2, 'C ', 400
union all select 3, 'A ', 500
union all select 3, 'B ', 600
union all select 3, 'D ', 700
declare @sql varchar(8000)
set @sql= 'select SO, '
select @[email protected]+quotename( 'CostGroup '+CostGroup)+ '=max(case when CostGroup= '+quotename(CostGroup, ' ' ' ')+ ' then CostAmount else 0 end), '
from T group by CostGroup
select @sql=left(@sql, len(@sql)-1), @[email protected]+ ' from T group by SO '
exec(@sql)
--result
SO CostGroupA CostGroupB CostGroupC CostGroupD
----------- ----------- ----------- ----------- -----------
1 100 200 0 0
2 0 300 400 0
3 500 600 0 700