Oracle SQL 行列转换详解

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

---

1. 概述

行列转换是常见需求[1]：

详细见：Oracle 行列转换详解。

---

2. 行转列（PIVOT）

2.1 PIVOT（11g+）

SELECT * FROM (
  SELECT dept_id, job_id, salary FROM employees
)
PIVOT (
  SUM(salary) FOR job_id IN (
    'IT_PROG' AS it,
    'SA_REP' AS sales,
    'ST_CLERK' AS clerk
  )
);

2.2 多聚合

SELECT * FROM (
  SELECT dept_id, job_id, salary FROM employees
)
PIVOT (
  SUM(salary) AS total, COUNT(*) AS cnt
  FOR job_id IN ('IT_PROG' AS it, 'SA_REP' AS sales)
);

2.3 多列

SELECT * FROM (
  SELECT dept_id, job_id, gender, salary FROM employees
)
PIVOT (
  SUM(salary)
  FOR (job_id, gender) IN (
    ('IT_PROG', 'M') AS it_m,
    ('IT_PROG', 'F') AS it_f,
    ('SA_REP', 'M') AS sales_m
  )
);

2.4 XML

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. 老式行转列

3.1 CASE

SELECT dept_id,
  SUM(CASE WHEN job_id = 'IT_PROG' THEN salary ELSE 0 END) AS it,
  SUM(CASE WHEN job_id = 'SA_REP' THEN salary ELSE 0 END) AS sales,
  SUM(CASE WHEN job_id = 'ST_CLERK' THEN salary ELSE 0 END) AS clerk
FROM employees
GROUP BY dept_id;

3.2 DECODE

SELECT dept_id,
  SUM(DECODE(job_id, 'IT_PROG', salary, 0)) AS it,
  SUM(DECODE(job_id, 'SA_REP', salary, 0)) AS sales,
  SUM(DECODE(job_id, 'ST_CLERK', salary, 0)) AS clerk
FROM employees
GROUP BY dept_id;

---

4. 列转行（UNPIVOT）

4.1 UNPIVOT（11g+）

-- 列转行
CREATE TABLE sales_pivot AS
SELECT * FROM (
  SELECT dept_id, q1, q2, q3, q4 FROM sales_summary
);

SELECT * FROM sales_pivot
UNPIVOT (
  amount FOR quarter IN (q1, q2, q3, q4)
);
-- 结果：dept_id, quarter, amount

4.2 包含 NULL

-- 默认排除 NULL
SELECT * FROM sales_pivot
UNPIVOT INCLUDE NULLS (
  amount FOR quarter IN (q1, q2, q3, q4)
);

4.3 多列

-- 多列转换
SELECT * FROM sales_pivot
UNPIVOT (
  (amount, count) FOR (q_amt, q_cnt) IN (
    (q1_amt, q1_cnt) AS 'Q1',
    (q2_amt, q2_cnt) AS 'Q2'
  )
);

---

5. UNION ALL

-- 老式列转行
SELECT dept_id, 'Q1' AS quarter, q1 AS amount FROM sales_summary
UNION ALL
SELECT dept_id, 'Q2' AS quarter, q2 AS amount FROM sales_summary
UNION ALL
SELECT dept_id, 'Q3' AS quarter, q3 AS amount FROM sales_summary
UNION ALL
SELECT dept_id, 'Q4' AS quarter, q4 AS amount FROM sales_summary;

---

6. 行转多列

6.1 案例

原始：
id  | name | dept
----+------+------
1   | A    | IT
2   | B    | IT
3   | C    | Sales

结果：
dept  | names
------+------
IT    | A,B
Sales | C

6.2 LISTAGG

SELECT dept, LISTAGG(name, ',') WITHIN GROUP (ORDER BY name) AS names
FROM t
GROUP BY dept;

详细见：Oracle SQL 函数大全详解。

6.3 XML

SELECT dept,
  RTRIM(XMLAGG(XMLELEMENT(e, name || ',').EXTRACT('//text()')).GETSTRINGVAL(), ',') AS names
FROM t
GROUP BY dept;

---

7. 字符串拆分

7.1 REGEXP

-- 拆分逗号分隔
SELECT REGEXP_SUBSTR('a,b,c,d', '[^,]+', 1, LEVEL) AS val
FROM dual
CONNECT BY LEVEL <= REGEXP_COUNT('a,b,c,d', ',') + 1;

7.2 JSON

-- 12c+
SELECT t.val
FROM json_table('["a","b","c"]', '$[*]'
  COLUMNS (val VARCHAR2(100) PATH '$')) t;

---

8. 多行合并

8.1 案例

原始：
id  | value
----+------
1   | A
1   | B
1   | C

结果：
id  | value
----+------
1   | A,B,C

8.2 LISTAGG

SELECT id, LISTAGG(value, ',') WITHIN GROUP (ORDER BY value) AS value
FROM t
GROUP BY id;

---

9. 多列合并

9.1 COALESCE

-- 多列非空
SELECT id, COALESCE(col1, col2, col3, 'N/A') AS value
FROM t;

9.2 UNPIVOT

SELECT id, value
FROM t
UNPIVOT (
  value FOR col IN (col1 AS 'C1', col2 AS 'C2', col3 AS 'C3')
);

---

10. 12c+ 增强

10.1 LISTAGG DISTINCT

SELECT LISTAGG(DISTINCT name, ',') WITHIN GROUP (ORDER BY name) ...

10.2 LISTAGG ON OVERFLOW

SELECT LISTAGG(name, ',' ON OVERFLOW TRUNCATE '...') WITHIN GROUP (ORDER BY name) ...

10.3 PIVOT 增强

- 多列
- XML
- 动态

---

11. 应用场景

11.1 报表

-- 月度销售
SELECT * FROM (
  SELECT TO_CHAR(sale_date, 'YYYY-MM') AS ym, region, amount FROM sales
)
PIVOT (
  SUM(amount) FOR region IN ('East' AS east, 'West' AS west, 'Central' AS central)
)
ORDER BY ym;

11.2 交叉表

-- 课程成绩
SELECT * FROM (
  SELECT student, course, score FROM scores
)
PIVOT (
  MAX(score) FOR course IN ('Math' AS math, 'English' AS english, 'Physics' AS physics)
);

11.3 数据清洗

-- 多行变一行
SELECT user_id, LISTAGG(role, ',') WITHIN GROUP (ORDER BY role) AS roles
FROM user_roles
GROUP BY user_id;

---

12. 性能

12.1 PIVOT

- 内部 GROUP BY
- 索引利用
- 高效

12.2 UNION ALL

- 多次扫描
- 性能差
- 数据量大慎用

12.3 LISTAGG

- 排序
- 内存
- 大数据 OVERFLOW

---

13. 常见坑与排错

13.1 PIVOT 列多

- IN 子句固定
- 动态需 XML / PL/SQL

13.2 LISTAGG 超长

- VARCHAR2(4000) 限制
- 12c+ ON OVERFLOW
- CLOB

13.3 UNPIVOT NULL

- 默认排除
- INCLUDE NULLS

---

14. 最佳实践

1. PIVOT/UNPIVOT：11g+
2. CASE 老式：兼容
3. LISTAGG：合并
4. DISTINCT：12c+
5. OVERFLOW：12c+
6. XML：动态
7. 索引：性能
8. 测试：验证
9. 性能：比较
10. 文档化：说明

---

15. 参考资料

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