目錄

一、原始數據

二、行轉列的多種實現方式

1.CASE WHEN

2.DECODE

3.PIVOT(Oracle獨有)

4.使用LEAD開窗函數

三、列轉行的多種實現方式

1.UNPIVOT(Oracle獨有)

2.UNION ALL合并結果集

四、行轉列練習：CASE WHEN/DECODE/PIVOT/lag/LEAD?

1.CASE WHEN

2.DECODE

3.PIVOT

4.LEAD

---

一、原始數據

CREATE TABLE T_SCORE (SNO NUMBER,CLA VARCHAR2(20),SCORE NUMBER);INSERT INTO T_SCORE VALUES (101,'語文',88);
INSERT INTO T_SCORE VALUES (101,'數學',89);
INSERT INTO T_SCORE VALUES (101,'英語',90);
INSERT INTO T_SCORE VALUES (102,'語文',91);
INSERT INTO T_SCORE VALUES (102,'數學',77);
INSERT INTO T_SCORE VALUES (102,'英語',56);
INSERT INTO T_SCORE VALUES (103,'語文',77);
INSERT INTO T_SCORE VALUES (103,'數學',88);
INSERT INTO T_SCORE VALUES (103,'英語',99);
INSERT INTO T_SCORE VALUES (104,'語文',77);
INSERT INTO T_SCORE VALUES (104,'數學',66);
INSERT INTO T_SCORE VALUES (104,'英語',55);
INSERT INTO T_SCORE VALUES (105,'語文',44);
INSERT INTO T_SCORE VALUES (105,'數學',67);
INSERT INTO T_SCORE VALUES (105,'英語',78);
INSERT INTO T_SCORE VALUES (106,'語文',89);
INSERT INTO T_SCORE VALUES (106,'數學',98);
INSERT INTO T_SCORE VALUES (106,'英語',78);
INSERT INTO T_SCORE VALUES (107,'語文',67);
INSERT INTO T_SCORE VALUES (107,'數學',56);
INSERT INTO T_SCORE VALUES (107,'英語',54);
INSERT INTO T_SCORE VALUES (108,'語文',76);
INSERT INTO T_SCORE VALUES (108,'數學',78);
INSERT INTO T_SCORE VALUES (108,'英語',12);
COMMIT;SELECT * FROM T_SCORE;

二、行轉列的多種實現方式

原格式：

行轉列后的格式：

1.CASE WHEN

select SNO, CASE WHEN CLA = '語文' THEN SCORE end as 語文, CASE WHEN CLA = '數學' THEN SCORE end as 數學, CASE WHEN CLA = '英語' THEN SCORE end as 英語
from T_SCORE;

?

下面兩種聚合函數都可以：?

select SNO, max(CASE WHEN CLA = '語文' THEN SCORE end) as 語文, max(CASE WHEN CLA = '數學' THEN SCORE end) as 數學, max(CASE WHEN CLA = '英語' THEN SCORE end) as 英語
from T_SCORE
group by SNO
order by SNO;select SNO, sum(CASE WHEN CLA = '語文' THEN SCORE end) as 語文, sum(CASE WHEN CLA = '數學' THEN SCORE end) as 數學, sum(CASE WHEN CLA = '英語' THEN SCORE end) as 英語
from T_SCORE
group by SNO
order by SNO;

二者區別：?

聚合函數	邏輯	處理重復記錄	適用場景
MAX	返回分組內的最大值。若每組只有一個值，則直接返回該值。	保留最大值（如補考成績）。	行轉列（提取唯一值）。
SUM	返回分組內所有值的總和。若每組只有一個值，則返回該值本身。	累加所有值（可能導致成績異常）。	統計總分或合計。

2.DECODE

select SNO, decode(CLA, '語文', SCORE) as 語文, decode(CLA, '數學', SCORE) as 數學, decode(CLA, '英語', SCORE) as 英語
from T_SCORE;

?

select SNO, max(decode(CLA, '語文', SCORE)) as 語文, max(decode(CLA, '數學', SCORE)) as 數學, max(decode(CLA, '英語', SCORE)) as 英語
from T_SCORE
group by SNO
order by SNO;select SNO, sum(decode(CLA, '語文', SCORE)) as 語文, sum(decode(CLA, '數學', SCORE)) as 數學, sum(decode(CLA, '英語', SCORE)) as 英語
from T_SCORE
group by SNO
order by SNO;

?

3.PIVOT(Oracle獨有)

語法：

PIVOT (SUM(聚合值) FOR 待轉換的列名 IN (待轉換的列名里面的值 轉換后列的別名))

select *
from T_SCOREPIVOT (sum(SCORE) for CLA in ( '語文' Chinese,'數學' Math,'英語' English))
order by SNO;

4.使用LEAD開窗函數

因為要對中文進行排序，所以先使用ASCII碼

SELECT CLA, ASCII(CLA)
FROM T_SCORE
GROUP BY CLA
ORDER BY CLA;

?

SELECT *
FROM (SELECT sno, LEAD(score, 0) OVER (PARTITION BY sno ORDER BY CLA ) 數學, LEAD(score, 1) OVER (PARTITION BY sno ORDER BY CLA ) 英語, LEAD(score, 2) OVER (PARTITION BY sno ORDER BY CLA ) 語文FROM T_SCORE)
WHERE 語文 IS NOT NULL;

?

三、列轉行的多種實現方式

DROP TABLE b_score;
CREATE TABLE b_score AS
SELECT *
FROM (SELECT *FROM t_scorePIVOT (SUM(score) -- 聚合函數（使用 SUM 或 MAX 均可）FOR cla IN ('語文' AS 語文, -- 指定課程名稱及對應的列別名'數學' AS 數學,'英語' AS 英語)))
ORDER BY sno; -- 按學生編號排序
COMMIT;
SELECT *
FROM b_score;

?原格式：

行轉列后的格式：

1.UNPIVOT(Oracle獨有)

語法：

UNPIVOT (存儲指標值的列名 FOR 合并后的列名 IN (待合并的列名))

SELECT *
FROM b_scoreUNPIVOT (score FOR cla IN (語文,數學,英語));

?

2.UNION ALL合并結果集

SELECT sno,'語文' cla,語文 score FROM b_score
UNION ALL
SELECT sno,'數學' cla,數學 score FROM b_score
UNION ALL
SELECT sno,'英語' cla,英語 score FROM b_score
order by SNO;

?

四、行轉列練習：CASE WHEN/DECODE/PIVOT/lag/LEAD?

輸出每個部門的總人數，展示樣式：

D10 ?D20 ?D30

? ?3? ? ? 5? ? ? ?6?

1.CASE WHEN

select COUNT(case when DEPTNO = 10 then 1 end) as D10, COUNT(case when DEPTNO = 20 then 1 end) as D20, COUNT(case when DEPTNO = 30 then 1 end) as D30
from EMP;

2.DECODE

select COUNT(DECODE(DEPTNO, 10, 1)) as D10, COUNT(DECODE(DEPTNO, 20, 1)) as D20, COUNT(DECODE(DEPTNO, 30, 1)) as D30
from EMP;

3.PIVOT

SELECT DEPTNO, COUNT(EMPNO) CM
FROM EMP
GROUP BY DEPTNO;

?

select *
from (SELECT DEPTNO, COUNT(EMPNO) CMFROM EMPGROUP BY DEPTNO)pivot (sum(cm) for DEPTNO in (10 D10,20 D20,30 D30));

?

4.LEAD

select DEPTNO, count(empno) ct
from EMP
group by DEPTNO;

?

SELECT *
FROM (select lead(ct, 0) over ( order by DEPTNO) as D10, lead(ct, 1) over ( order by DEPTNO) as D20, lead(ct, 2) over ( order by DEPTNO) as D30from (select DEPTNO, count(empno) ctfrom EMPgroup by DEPTNO)) a
WHERE D30 IS NOT NULL;

?