一个有难道的sql写法,高分求解

[qixincomputer2000]

有如下表
id year month amount
1 2004 1 100
2 2004 2 100
3 2004 3 100
4 2004 4 100
5 2004 5 100
6 2004 6 100
7 2004 7 100
8 2004 8 100
9 2004 9 100
10 2004 10 100
11 2004 11 100
12 2004 12 100

用一句sql 得到如下效果

year month1 month2 month3 month4 month5 month6 month7 month8 month9
2004 100 100 100 100 100 100 100 100 100
month10 month11 month12
100 100 100

[zjcxc]

select [year]
,month1=sum(case [month] when 1 then amount else 0 end)
,month2=sum(case [month] when 2 then amount else 0 end)
,month3=sum(case [month] when 3 then amount else 0 end)
,month4=sum(case [month] when 4 then amount else 0 end)
,month5=sum(case [month] when 5 then amount else 0 end)
,month6=sum(case [month] when 6 then amount else 0 end)
,month7=sum(case [month] when 7 then amount else 0 end)
,month8=sum(case [month] when 8 then amount else 0 end)
,month9=sum(case [month] when 9 then amount else 0 end)
,month10=sum(case [month] when 10 then amount else 0 end)
,month11=sum(case [month] when 11 then amount else 0 end)
,month12=sum(case [month] when 12 then amount else 0 end)
from 表
group by [year]

[zjcxc]

--测试

--测试数据
create table 表(id int,[year] int,[month] int,amount int)
insert 表 select 1 ,2004,1 ,100
union all select 2 ,2004,2 ,100
union all select 3 ,2004,3 ,100
union all select 4 ,2004,4 ,100
union all select 5 ,2004,5 ,100
union all select 6 ,2004,6 ,100
union all select 7 ,2004,7 ,100
union all select 8 ,2004,8 ,100
union all select 9 ,2004,9 ,100
union all select 10,2004,10,100
union all select 11,2004,11,100
union all select 12,2004,12,100
go

--查询
select [year]
,month1=sum(case [month] when 1 then amount else 0 end)
,month2=sum(case [month] when 2 then amount else 0 end)
,month3=sum(case [month] when 3 then amount else 0 end)
,month4=sum(case [month] when 4 then amount else 0 end)
,month5=sum(case [month] when 5 then amount else 0 end)
,month6=sum(case [month] when 6 then amount else 0 end)
,month7=sum(case [month] when 7 then amount else 0 end)
,month8=sum(case [month] when 8 then amount else 0 end)
,month9=sum(case [month] when 9 then amount else 0 end)
,month10=sum(case [month] when 10 then amount else 0 end)
,month11=sum(case [month] when 11 then amount else 0 end)
,month12=sum(case [month] when 12 then amount else 0 end)
from 表
group by [year]
go

--删除测试
drop table 表

/*--测试结果
year month1 month2 month3 month4 month5 month6 month7 month8 month9 month10 month11 month12
----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
2004 100 100 100 100 100 100 100 100 100 100 100 100

（所影响的行数为 1 行）
--*/

[qixincomputer2000]

谢谢zjcxc(邹建)，这就是我想要的答案。