sql 行转列

将表1变成表2

表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

----------------------------------

表2

----------------------------------

year      m1         m2          m3          m4

1991     1.1        1.2          1.3          1.4

1992     2.1        2.2          2.3          2.4

----------------------------------

语句1

select t.year,

(select m1.amount from table m1 where m1.year = t.year and m1.month = t.month) m1, 
(select m2.amount from table m2 where m2.year = t.year and m2.month = t.month) m2, 
(select m3.amount from table m3 where m3.year = t.year and m3.month = t.month) m3, 
(select m4.amount from table m4 where m4.year = t.year and m4.month = t.month) m4 
from table t order by t.year

语句2

select t.year,

max(case subject when "1" then amount else null end)m1,

max(case subject when "2" then amount else null end)m2,

max(case subject when "3" then amount else null end)m3,

max(case subject when "4" then amount else null end)m4

from table t group by t.year

什么时候用pivot，待续