decode函数应用，对角线算法实现表中数据的行列转换

将下表用一条语句

Year	month	XX
1921	1	1.1
1921	2	1.2
1921	3	1.3
1921	4	1.4
1922	1	2.1
1922	2	2.2
1922	3	2.3
1922	4	2.4

转化为下表：

year	M1	M2	M3	M4
1921	1.1	1.2	1.3	1.4
1922	2.1	2.2	2.3	2.4

 

 

 

 

select b.y,sum(mm1) as m1

          ,sum(mm2) as m2

          ,sum(mm3) as m3

          ,sum(mm4) as m4

    from(

          select year as y

            ,DECODE( MONTH,'1',XX,0) as mm1

            ,DECODE ( MONTH,'2',XX,0) as mm2

            ,DECODE ( MONTH,'3',XX,0) as mm3

            ,DECODE ( MONTH,'4',XX,0) as mm4

         from SCOTT.XX ) b group by  b.y