把某一字段值做为列的查询
有表结构如下
SOCostGroupCostAmount
1A100
1B200
2B300
2C400
3A500
3B600
3D700
要查询出如下格式:
SO CostGroupA CostGroupBCostGroupC CostGroupD
1 100 2000 0
2 0 300400 0
3 500 6000 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 @sql=@sql+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), @sql=@sql+ ' 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
