统计列表如下
表中有：
Name   sid   aid
aaa     1         1
aaa     2         1
aaa     3         2
bbb     1         2
bbb     2         3
ccc     1         2
ccc     2         5
ccc     3         2
......
什么语句可以形成：
Name   aid1   aid2   aid3  
aaa     1         1           2
bbb     2         3          
ccc     2         5           2
......

------解决方案--------------------
select name,sum(case when sid=1 then aid else 0 end)aid1,
sum(case when sid=2 then aid else 0 end)aid2,
sum(case when sid=3 then aid else 0 end)aid3
from 表 group by name
------解决方案--------------------
create table test(name varchar(10),sid int,aid int)
insert test select 'aaa ',1,1
union all select 'aaa ',2,1
union all select 'aaa ',3,2
union all select 'bbb ',1,2
union all select 'bbb ',2,3
union all select 'ccc ',1,2
union all select 'ccc ',2,5
union all select 'ccc ',3,2

declare @s varchar(8000)
set @s= 'select name '
select @s=@s+ ',max(case bh when ' ' '+rtrim(bh)+ ' ' ' then aid else 0 end) as aid '+rtrim(bh)+ ' '
from (select *,bh=(select count(1) from test where name=b1.name and sid <=b1.sid) from test b1)t group by bh

select @s=@s+ ' from (select *,bh=(select count(1) from test where name=b1.name and sid <=b1.sid) from test b1)t group by name '
exec(@s)

drop table test
------解决方案--------------------
create table T(Name nvarchar(10),sid int,aid int)
insert T select 'aaa ', 1, 1
union all select 'aaa ', 2, 1
union all select 'aaa ', 3, 2
union all select 'bbb ', 1, 2
union all select 'bbb ', 2, 3
union all select 'ccc ', 1, 2
union all select 'ccc ', 2, 5
union all select 'ccc ', 3, 2

declare @sql nvarchar(4000)
set @sql= 'select name, '
select @sql=@sql+quotename( 'aid '+rtrim(sid))+ '=max(case when sid= '+rtrim(sid)+ ' then aid else 0 end), '
from T
group by sid
select @sql=left(@sql,len(@sql)-1), @sql=@sql+ ' from T group by name '
exec(@sql)

--result
name aid1 aid2 aid3
---------- ----------- ----------- -----------
aaa 1 1 2
bbb 2 3 0
ccc 2 5 2

------解决方案--------------------
create table t(xh varchar(20),kc varchar(20),cj int)
insert t select '051000333 ', '高等数学 ',55
union all select '051000333 ', '大学语文 ',67
union all select '051000333 ', '经济学基础 ',88
union all select '021000224 ', '高等数学 ',64
union all select '021000224 ', '大学语文 ',32
union all select '021000224 ', '经济学基础 ',75
union all select '041000851 ', '高等数学 ',69