Oracle PIVOT/UNPIVOT 完全指南：行列轉換、多維聚合與反轉實戰

Oracle PIVOT/UNPIVOT 完全指南：行列轉換、多維聚合與反轉實戰

Oracle 11g 引入 PIVOT/UNPIVOT，以交叉表查詢方式將行轉列、列轉行，極大簡化以往需大量 DECODE/CASE 的報表邏輯。本文從語法、單欄/多欄轉換、多聚合函數、UNPIVOT 反轉四大場景實戰演示。

基礎語法

SELECT ... FROM table_expr
PIVOT ( aggregate_function(column) FOR pivot_column IN (value1 AS alias1, ...) ) alias
WHERE ...

準備測試資料

CREATE VIEW emp_view AS
SELECT deptno, job, TO_CHAR(hiredate,'yyyy') hiredate,
       COUNT(*) cnt, SUM(sal) sum_sal
FROM emp
GROUP BY deptno, job, TO_CHAR(hiredate,'yyyy');

場景一：基本 PIVOT（單欄行轉列）

-- 依 hiredate 聚合 cnt
SELECT * FROM (
  SELECT deptno, hiredate, cnt FROM emp_view
)
PIVOT ( SUM(cnt) FOR hiredate IN ('1980' AS "1980", '1981' AS "1981", '1982' AS "1982", '1987' AS "1987") )
ORDER BY deptno;

-- 依 job 聚合 cnt（無別名，欄位名直接為 job 值）
SELECT * FROM (
  SELECT deptno, job, cnt FROM emp_view
)
PIVOT ( SUM(cnt) FOR job IN ('CLERK','ANALYST','MANAGER','SALESMAN','PRESIDENT') )
ORDER BY deptno;

場景二：多欄 PIVOT（複合鍵行轉列）

SELECT * FROM (
  SELECT deptno, job, hiredate, cnt FROM emp_view
)
PIVOT ( SUM(cnt) FOR (job, hiredate) IN (
  ('CLERK','1980') AS clerk_1980,
  ('CLERK','1981') AS clerk_1981,
  ('ANALYST','1987') AS analyst_1987,
  ('MANAGER','1981') AS manager_1981
) )
ORDER BY deptno;

可從部門、職稱、年份三個維度呈現統計。

場景三：多聚合函數（同次轉換產出多指標）

SELECT * FROM (
  SELECT deptno, hiredate, cnt, sum_sal FROM emp_view
)
PIVOT (
  SUM(cnt)     AS cnt,
  SUM(sum_sal) AS sum_sal
  FOR hiredate IN ('1980','1981','1982','1987')
)
ORDER BY deptno;

產出欄位：1980_CNT, 1980_SUM_SAL, 1981_CNT, 1981_SUM_SAL ...

UNPIVOT：列轉行（反轉操作）

-- 建立 PIVOT 結果表 T1
CREATE TABLE T1 AS ...（上述多聚合結果）;

-- 一維 UNPIVOT：只還原 cnt
SELECT deptno,
       DECODE(hiredate,'1980_CNT','1980','1981_CNT','1981','1982_CNT','1982','1987_CNT','1987') AS hiredate,
       cnt
FROM T1
UNPIVOT INCLUDE NULLS ( cnt FOR hiredate IN ("1980_CNT","1981_CNT","1982_CNT","1987_CNT") );

-- 二維 UNPIVOT：同時還原 cnt 與 sum_sal
SELECT deptno, hiredate, cnt, sum_sal
FROM T1
UNPIVOT ( (cnt,sum_sal) FOR hiredate IN (
  ("1980_CNT","1980_SUM_SAL") AS 1980,
  ("1981_CNT","1981_SUM_SAL") AS 1981,
  ("1982_CNT","1982_SUM_SAL") AS 1982,
  ("1987_CNT","1987_SUM_SAL") AS 1987 ) );

關鍵參數

• INCLUDE NULLS：保留 NULL 值列；預設 EXCLUDE NULLS 會過濾掉。
• 別名建議加雙引號保留大小寫：AS "1980"。