把某一字段值做为列的查询
有表结构如下
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 @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