搜狐畅游的一道SQL笔试题

表名为table的表内容如下
Year month value
2009  1    1.1
2009  2    1.2
2009  3    1.3
2009  4    1.4
2010  1    2.1
2010  2    2.2
2010  3    2.3
2010  4    2.4


要求查询结果为

year  m1  m2  m3  m4
2009  1.1  1.2  1.3  1.4
2010  2.1  2.2  2.3  2.4

---

SQL code:
select year,sum(case when month=1 then value) as m1
,sum(case when month=2 then value) as m2
,sum(case when month=3 then value) as m3
,sum(case when month=4 then value) as m4
from tb
group by year

---

select year,[m1]=max(case when year=[year] and month='1' then value end),
[m2]=max(case when year=[year] and month='2' then value end),
[m3]=max(case when year=[year] and month='3' then value end),
[m4]=max(case when year=[year] and month='4' then value end)
from tb
group by year

---

SQL code:

declare @table table ([Year] int,[month] int,[value] numeric(2,1))
in