select year
(select price from test where year=o.year and moth='1') moth1,
(select price from test where year=o.year and moth='2') moth2,
(select price from test where year=o.year and moth ='3') moth3,
(select price from test where year=o.year and moth ='4') moth4
from test o group by year
select year,
[1] as "moth1",
[2] as " moth2",
[3] as " moth3",
[4] as " moth4"
from
test
pivot
( price for moth in ([1],[2],[3],[4])
) as pvt
select year,
(case when moth=1 then price end) as moth1,
(case when moth=2 then price end) as moth2,
(case when moth=3 then price end) as moth3,
(case when moth=4 then price end) as moth4
from table
year moth price
2000 1 1.1
2000 2 1.2
2000 3 1.3
2000 4 1.4
2002 1 1.1
2002 2 1.2
2002 3 1.3
结果是：
year moth1 moth2 moth3 moth4
2000 1.1 1.2 1.3 1.4
2002 1.1 1.2 1.3 0.0