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;
![](https://img2020.cnblogs.com/blog/2157710/202101/2157710-20210109193452905-671937194.png)
SELECT * FROM T_COLUMN;
![](https://img2020.cnblogs.com/blog/2157710/202101/2157710-20210109193532709-1786086473.png)
表也建好了,数据也插入完成了,接下来就可以进行行列转换操作啦!!!
行转列
方法一:使用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;