SQL Server 面試題（1）

面試題：怎麼把這樣一個表兒
year month amount
1991   1     1.1
1991   2     1.2
1991   3     1.3
1991   4     1.4
1992   1     2.1
1992   2     2.2
1992   3     2.3
1992   4     2.4
查成這樣一個結果
year m1 m2 m3 m4
1991 1.1 1.2 1.3 1.4
1992 2.1 2.2 2.3 2.4

答案

 

代碼

select year,
(select amount from t m where month='1' and m.year = t.year) as m1,
(select amount from t m where month='2' and m.year = t.year) as m2,
(select amount from t m where month='3' and m.year = t.year) as m2,
(select amount from t m where month='4' and m.year = t.year) as m2
from t group by year