PIVOT

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

posted @ 2009-01-05 20:22  HiEagle  阅读(240)  评论(0编辑  收藏  举报