Oracle行转列和列转行的方式
--列转行(数据对称型)
--需求:将test的数据转换为如下所示:
drop table test; CREATE TABLE TEST ( YEARS NUMBER, Q NUMBER, AMT NUMBER ); INSERT INTO TEST(YEARS,Q,AMT) VALUES(2020,1,2000); INSERT INTO TEST(YEARS,Q,AMT) VALUES(2020,2,3500); INSERT INTO TEST(YEARS,Q,AMT) VALUES(2020,3,4000); INSERT INTO TEST(YEARS,Q,AMT) VALUES(2020,4,5000); INSERT INTO TEST(YEARS,Q,AMT) VALUES(2021,1,1500); INSERT INTO TEST(YEARS,Q,AMT) VALUES(2021,2,3000); INSERT INTO TEST(YEARS,Q,AMT) VALUES(2021,3,4000); INSERT INTO TEST(YEARS,Q,AMT) VALUES(2021,4,5000); COMMIT;
--方法一:使用LEAD函数(只适用于数据对称的列转行)
SELECT YEARS AS YEAR, AMT Q1, L1 Q2, L2 Q3, L3 Q4 FROM (SELECT T.*, LEAD(AMT,1) OVER(PARTITION BY YEARS ORDER BY Q) L1, LEAD(AMT,2) OVER(PARTITION BY YEARS ORDER BY Q) L2, LEAD(AMT,3) OVER(PARTITION BY YEARS ORDER BY Q) L3 FROM TEST T) A WHERE Q=1;
--方法二:使用LAG函数(只适用于数据对称的列转行)
SELECT YEARS AS YEAR, L3 Q1, L2 Q2, L1 Q3, AMT Q4 FROM (SELECT T.*, LAG(AMT,1) OVER(PARTITION BY YEARS ORDER BY Q) L1, LAG(AMT,2) OVER(PARTITION BY YEARS ORDER BY Q) L2, LAG(AMT,3) OVER(PARTITION BY YEARS ORDER BY Q) L3 FROM TEST T) A WHERE Q=4;
--方法三:使用CASE WHEN函数(适用于所有情况的列转行)
SELECT YEARS AS YEAR, SUM(CASE WHEN Q=1 THEN AMT ELSE NULL END) AS Q1, SUM(CASE WHEN Q=2 THEN AMT ELSE NULL END) AS Q2, SUM(CASE WHEN Q=3 THEN AMT ELSE NULL END) AS Q3, SUM(CASE WHEN Q=4 THEN AMT ELSE NULL END) AS Q4 FROM TEST GROUP BY YEARS;
--方法四:使用DECODE函数(在Oracle中适用于所有情况的列转行)
SELECT YEARS AS YEAR, MAX(DECODE(Q,1,AMT)) Q1, MAX(DECODE(Q,2,AMT)) Q2, MAX(DECODE(Q,3,AMT)) Q3, MAX(DECODE(Q,4,AMT)) Q4 FROM TEST GROUP BY YEARS;
--方法五:使用自关联(只适用于数据对称的列转行)
SELECT A.YEARS AS YEAR, A.AMT AS Q1, B.AMT AS Q2, C.AMT AS Q3, D.AMT AS Q4 FROM (SELECT * FROM TEST WHERE Q=1) A JOIN (SELECT * FROM TEST WHERE Q=2) B ON A.YEARS=B.YEARS JOIN (SELECT * FROM TEST WHERE Q=3) C ON A.YEARS=C.YEARS JOIN (SELECT * FROM TEST WHERE Q=4) D ON A.YEARS=D.YEARS;
--方法六:使用pivot
select * from test pivot (max(amt) for Q in ('1' as Q1 ,'2' as Q2,'3' as Q3,'4' as Q4));
-------行转列,使用unpivot
SELECT YEARS,Q,AMT FROM TEST1 UNPIVOT (AMT FOR Q in (Q1,Q2,Q3,Q4));