Oracle行列转换

行列转换

在用户制作数据报表时，经常会使用到表数据的行列转换操作！

这里我们创建两个表，插入数据，用于实现下面的行列转换操作。

CREATE TABLE T_ROW(
   Y  NUMBER,
   Q  NUMBER,
   AMT NUMBER
);

INSERT INTO T_ROW (Y, Q, AMT) VALUES(2015,  1 ,100 );
INSERT INTO T_ROW (Y, Q, AMT) VALUES(2015,  2 ,110 );
INSERT INTO T_ROW (Y, Q, AMT) VALUES(2015,  3 ,130 );
INSERT INTO T_ROW (Y, Q, AMT) VALUES(2015,  4 ,100 );
INSERT INTO T_ROW (Y, Q, AMT) VALUES(2016,  1 ,200 );
INSERT INTO T_ROW (Y, Q, AMT) VALUES(2016,  2 ,150 );
INSERT INTO T_ROW (Y, Q, AMT) VALUES(2016,  3 ,100 );
INSERT INTO T_ROW (Y, Q, AMT) VALUES(2016,  4 ,300 );
COMMIT;

CREATE TABLE T_COLUMN(
    Y NUMBER,
    Q1 NUMBER,
    Q2 NUMBER,
    Q3 NUMBER,
    Q4 NUMBER
);

INSERT INTO T_COLUMN (Y, Q1, Q2, Q3, Q4) VALUES(2015,100,110,130,100);
INSERT INTO T_COLUMN (Y, Q1, Q2, Q3, Q4) VALUES(2016,200,150,100,300);
COMMIT;

SELECT * FROM T_ROW;

SELECT * FROM T_COLUMN;

表也建好了，数据也插入完成了，接下来就可以进行行列转换操作啦！！！

行转列

方法一：使用CASE WHEN实现

SELECT T.Y,
       MAX(CASE
             WHEN T.Q = 1 THEN
              T.AMT
             ELSE
              0
           END) Q1,
       MAX(CASE
             WHEN T.Q = 2 THEN
              T.AMT
             ELSE
              0
           END) Q2,
       MAX(CASE
             WHEN T.Q = 3 THEN
              T.AMT
             ELSE
              0
           END) Q3,
       MAX(CASE
             WHEN T.Q = 4 THEN
              T.AMT
             ELSE
              0
           END) Q4
  FROM T_ROW T
 GROUP BY T.Y

方法二：使用DECODE实现

SELECT T.Y,
       SUM(DECODE(T.Q, 1, T.AMT, 0)) Q1,
       SUM(DECODE(T.Q, 2, T.AMT, 0)) Q2,
       SUM(DECODE(T.Q, 3, T.AMT, 0)) Q3,
       SUM(DECODE(T.Q, 4, T.AMT, 0)) Q4
  FROM T_ROW T
 GROUP BY T.Y;

方法三：使用位移函数的上移函数（LEAD函数）实现

WITH TMP_ROW AS
 (SELECT T.Y,
         T.Q,
         T.AMT,
         LEAD(T.AMT, 1) OVER(PARTITION BY T.Y ORDER BY T.Q) LEAD1,
         LEAD(T.AMT, 2) OVER(PARTITION BY T.Y ORDER BY T.Q) LEAD2,
         LEAD(T.AMT, 3) OVER(PARTITION BY T.Y ORDER BY T.Q) LEAD3
    FROM T_ROW T)
SELECT Y, 
       AMT Q1, 
       LEAD1 Q2, 
       LEAD2 Q3, 
       LEAD3 Q4 
  FROM TMP_ROW 
 WHERE Q = 1;

方法四：使用位移函数的下移函数（LAG函数）实现

WITH TMP_ROW AS
 (SELECT T.Y,
         T.Q,
         T.AMT,
         LAG(T.AMT, 1) OVER(PARTITION BY T.Y ORDER BY T.Q DESC) LAG1,
         LAG(T.AMT, 2) OVER(PARTITION BY T.Y ORDER BY T.Q DESC) LAG2,
         LAG(T.AMT, 3) OVER(PARTITION BY T.Y ORDER BY T.Q DESC) LAG3
    FROM T_ROW T)
SELECT Y, 
       AMT Q1,
       LAG1 Q2, 
       LAG2 Q3, 
       LAG3 Q4
  FROM TMP_ROW 
 WHERE Q = 1;

方法五：使用ORACLE独有的函数PIVOT实现

SELECT *
FROM T_ROW 
PIVOT(SUM(AMT) FOR Q IN (1 Q1,2 Q2,3 Q3,4 Q4));

列转行

方法一：使用ORACLE独有的函数UNPIVOT实现

SELECT Y,Q,AMT
FROM (SELECT Y,Q1,Q2,Q3,Q4 FROM T_COLUMN)
UNPIVOT(AMT FOR Q IN (Q1 AS 1,Q2 AS 2,Q3 AS 3,Q4 AS 4));

方法二：使用UNION ALL实现

WITH TMP_COLUMN AS
 (SELECT T.Y, 1 Q, T.Q1 AMT
    FROM T_COLUMN T
  UNION ALL
  SELECT T.Y, 2 Q, T.Q2 AMT
    FROM T_COLUMN T
  UNION ALL
  SELECT T.Y, 3 Q, T.Q3 AMT
    FROM T_COLUMN T
  UNION ALL
  SELECT T.Y, 4 Q, T.Q4 AMT
    FROM T_COLUMN T)
SELECT Y, 
       Q, 
       AMT 
  FROM TMP_COLUMN 
 ORDER BY Y, Q;

Oracle行列转换

推荐阅读