SQL Server interview questions (1)

Interview question: how to put such a table
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
Query such a result
Year m 1 M2 m3 m4
1991 1.1 1.2 1.3 1.4
1992 2.1 2.2 2.3 2.4

Answer

 

Code

 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