SQL SERVER行列转换

 

把如下表的

            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 

            转换成这样一个结果 

            year    m1      m2    m3     m4 

            1991    1.1     1.2     1.3    1.4 

            1992    2.1     2.2     2.3    2.4

 

sql1:

select year,
sum(case when month=1 then cast(amount as float) end) m1,
sum(case when month=2 then cast(amount as float) end) m2,
sum(case when month=3 then cast(amount as float) end) m3,
sum(case when month=4 then cast(amount as float) end) m4
from dbo.T_cdate
group by year

sql2:

select year,[1] as m1,[2] as m2,[3] as m3,[4] as m
from (select year,month,amount from dbo.T_cdate) p
pivot(sum(amount) for month
in([1],[2],[3],[4])) as pvt
group by year