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