Oracle 行列转换（PIVOT / UNPIVOT）

适用版本：Oracle Database 11g / 12c / 19c / 23ai 文档版本：v1.0 / 2026-07

---

1. 概述

• PIVOT：行转列
• UNPIVOT：列转行

---

2. PIVOT

2.1 基本语法

SELECT * FROM (
  SELECT dept_id, job_id, salary
  FROM employees
)
PIVOT (
  SUM(salary)
  FOR job_id IN ('MGR' AS mgr, 'ANALYST' AS analyst, 'CLERK' AS clerk)
);

2.2 输出

dept_id | mgr   | analyst | clerk
10      | 5000  |         | 3000
20      | 6000  | 8000    | 4000
30      | 7000  |         | 3500

2.3 多聚合

SELECT * FROM (
  SELECT dept_id, job_id, salary
  FROM employees
)
PIVOT (
  SUM(salary) AS total,
  COUNT(*) AS cnt
  FOR job_id IN ('MGR', 'ANALYST', 'CLERK')
);

2.4 多列

SELECT * FROM (
  SELECT dept_id, year, quarter, amount
  FROM sales
)
PIVOT (
  SUM(amount)
  FOR (year, quarter) IN (
    (2025, 'Q1') AS y2025_q1,
    (2025, 'Q2') AS y2025_q2,
    (2026, 'Q1') AS y2026_q1
  )
);

2.5 XML PIVOT

SELECT * FROM (
  SELECT dept_id, job_id, salary
  FROM employees
)
PIVOT XML (
  SUM(salary)
  FOR job_id IN (SELECT DISTINCT job_id FROM employees)
);

---

3. UNPIVOT

3.1 基本语法

-- 原始
-- dept_id | mgr  | analyst | clerk
-- 10      | 5000 | NULL    | 3000

SELECT * FROM pivoted_data
UNPIVOT (
  salary FOR job_id IN (mgr AS 'MGR', analyst AS 'ANALYST', clerk AS 'CLERK')
);

-- 输出
-- dept_id | job_id | salary
-- 10      | MGR    | 5000
-- 10      | CLERK  | 3000

3.2 INCLUDE NULLS

-- 默认排除 NULL
-- INCLUDE NULLS 包含
SELECT * FROM pivoted_data
UNPIVOT INCLUDE NULLS (
  salary FOR job_id IN (mgr, analyst, clerk)
);

---

4. 传统方法（11g 之前）

4.1 行转列（CASE）

SELECT 
  dept_id,
  SUM(CASE WHEN job_id = 'MGR' THEN salary END) AS mgr,
  SUM(CASE WHEN job_id = 'ANALYST' THEN salary END) AS analyst,
  SUM(CASE WHEN job_id = 'CLERK' THEN salary END) AS clerk
FROM employees
GROUP BY dept_id;

4.2 列转行（UNION ALL）

SELECT dept_id, 'MGR' AS job_id, mgr AS salary FROM pivoted_data WHERE mgr IS NOT NULL
UNION ALL
SELECT dept_id, 'ANALYST', analyst FROM pivoted_data WHERE analyst IS NOT NULL
UNION ALL
SELECT dept_id, 'CLERK', clerk FROM pivoted_data WHERE clerk IS NOT NULL;

---

5. 应用场景

5.1 月度报表

-- 月度销售
SELECT * FROM (
  SELECT 
    product_id,
    EXTRACT(MONTH FROM sale_date) AS month,
    amount
  FROM sales
  WHERE sale_date >= TRUNC(SYSDATE, 'YYYY')
)
PIVOT (
  SUM(amount)
  FOR month IN (
    1 AS jan, 2 AS feb, 3 AS mar, 4 AS apr,
    5 AS may, 6 AS jun, 7 AS jul, 8 AS aug,
    9 AS sep, 10 AS oct, 11 AS nov, 12 AS dec
  )
);

5.2 交叉表

-- 部门 vs 职位
SELECT * FROM (
  SELECT dept_id, job_id
  FROM employees
)
PIVOT (
  COUNT(*)
  FOR job_id IN ('MGR', 'ANALYST', 'CLERK', 'SALESMAN')
);

5.3 KPI 对比

-- 多指标对比
SELECT * FROM (
  SELECT dept_id, metric_name, metric_value
  FROM kpi_data
)
PIVOT (
  MAX(metric_value)
  FOR metric_name IN ('revenue', 'profit', 'cost')
);

---

6. PIVOT 限制

• IN 列表必须明确
• 不能使用子查询（除 XML）
• 列名有限制

---

7. 常见坑与排错

7.1 ORA-00904: 无效标识符

-- 检查列名
-- 别名要符合命名规范

7.2 列名冲突

-- 使用别名
PIVOT (SUM(salary) FOR job_id IN ('MGR' AS mgr_sal))

7.3 数据类型

-- PIVOT 聚合结果类型
-- 注意 NUMBER vs 其他

---

8. 最佳实践

1. 11g+ 用 PIVOT/UNPIVOT：简洁
2. 旧版用 CASE/UNION ALL：兼容
3. 聚合明确：SUM/AVG/COUNT
4. 别名规范：易读
5. NULL 处理：UNPIVOT
6. XML 动态：动态列
7. 测试结果：验证
8. 性能考虑：大数据量

---

9. 参考资料

[1] Oracle Database SQL Language Reference 19c, “PIVOT and UNPIVOT” https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/SELECT.html