行转列的多种方法探究

本文所使用的创表语句如下：

create table umo (
    Y number(4),
    Q number(4),
    AMT number(4)
)
insert into umo values(2015,1,100);
insert into umo values(2015,2,110);
insert into umo values(2015,3,130);
insert into umo values(2015,4,100);
insert into umo values(2016,1,100);
insert into umo values(2016,2,150);
insert into umo values(2016,3,100);
insert into umo values(2016,4,300);

---

方法一：使用lead迁移函数

 SELECT S.Y, S.AMT Q1, S.LD1 Q2, S.LD2 Q3, S.LD3 Q4
  FROM (SELECT umo.*,
               LEAD(umo.AMT, 1) OVER(PARTITION BY umo.Y ORDER BY umo.Q) LD1,
               LEAD(umo.AMT, 2) OVER(PARTITION BY umo.Y ORDER BY umo.Q) LD2,
               LEAD(umo.AMT, 3) OVER(PARTITION BY umo.Y ORDER BY umo.Q) LD3
          FROM umo) S
 WHERE S.Q = 1

迁移函数将Q=2,Q=3,Q=4的AMT值都移向同一行，之后截取 Q=1即可；

方法二 ：使用多表关联

SELECT umo.Y, umo.AMT Q1, A.AMT Q2, B.AMT Q3, C.AMT Q4
   FROM umo,
        (SELECT * FROM umo WHERE umo.Q = 2) A,
        (SELECT * FROM umo WHERE umo.Q = 3) B,
        (SELECT * FROM umo WHERE umo.Q = 4) C
  WHERE umo.Y = A.Y
    AND umo.Y = B.Y
    AND umo.Y = C.Y
    AND umo.Q=1

此方法将Q=2,Q=3,Q=4分别成表筛选出来，进行表关联，最后同样是选择Q=1即可；

方法三：使用decode判断

 SELECT umo.Y,
       SUM(DECODE(umo.Q, 1, AMT, NULL)) Q1,
       SUM(DECODE(umo.Q, 2, AMT, NULL)) Q2,
       SUM(DECODE(umo.Q, 3, AMT, NULL)) Q3,
       SUM(DECODE(umo.Q, 4, AMT, NULL)) Q4
  FROM umo
 GROUP BY umo.Y

需注意，null数据库解释为：不知道，不参与聚合函数的运算

方法四 case when 判断

select umo.Y,
    max(case when umo.Q=1 then AMT else null end) Q1,
    max(case when umo.Q=2 then AMT else null end) Q2,
    max(case when umo.Q=3 then AMT else null end) Q3,
    max(case when umo.Q=4 then AMT else null end) Q4
    from umo
    GROUP BY umo.Y

基本思路和使用decode判断相同，case when 的语句更具有普适性，不仅仅适用于ORACLE

方法五 pivot函数

select * from umo pivot(SUM(AMT) FOR Q IN (1 AS Q1 , 2 AS Q2, 3 AS Q3 , 4 AS Q4 )) ORDER BY Y

使用pivot函数最为简洁，注意一定要使用聚合函数哦！