将表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，待续
原文：http://www.cnblogs.com/helloyyn/p/4363412.html