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));

 

posted @ 2022-05-08 20:30  潜摩羯  阅读(6823)  评论(0编辑  收藏  举报