Oracle SQL 進階：行列轉換（PIVOT/UNPIVOT）與字符串聚合/分割實戰

準備數據

DROP TABLE t_change_lc;
CREATE TABLE t_change_lc (card_code VARCHAR2(3), q NUMBER, bal NUMBER);

INSERT INTO t_change_lc
SELECT '001' card_code, ROWNUM q, TRUNC(DBMS_RANDOM.VALUE * 100) bal FROM dual CONNECT BY ROWNUM <= 4
UNION ALL
SELECT '002' card_code, ROWNUM q, TRUNC(DBMS_RANDOM.VALUE * 100) bal FROM dual CONNECT BY ROWNUM <= 4;

1. 行轉列（PIVOT）：將季度行轉為列

傳統 DECODE/CONTIONAL AGGREGATION 寫法

SELECT a.card_code,
  SUM(DECODE(a.q, 1, a.bal, 0)) q1,
  SUM(DECODE(a.q, 2, a.bal, 0)) q2,
  SUM(DECODE(a.q, 3, a.bal, 0)) q3,
  SUM(DECODE(a.q, 4, a.bal, 0)) q4
FROM t_change_lc a
GROUP BY a.card_code
ORDER BY 1;

Oracle 11g+ PIVOT 語法（更簡潔）

SELECT * FROM t_change_lc
PIVOT (SUM(bal) FOR q IN (1 AS q1, 2 AS q2, 3 AS q3, 4 AS q4))
ORDER BY card_code;

2. 列轉行（UNPIVOT）：將列轉為行

準備寬表

DROP TABLE t_change_cl;
CREATE TABLE t_change_cl AS
SELECT a.card_code,
  SUM(DECODE(a.q, 1, a.bal, 0)) q1,
  SUM(DECODE(a.q, 2, a.bal, 0)) q2,
  SUM(DECODE(a.q, 3, a.bal, 0)) q3,
  SUM(DECODE(a.q, 4, a.bal, 0)) q4
FROM t_change_lc a
GROUP BY a.card_code
ORDER BY 1;

傳統 UNION ALL 寫法

SELECT card_code, 1 AS q, q1 AS bal FROM t_change_cl UNION ALL
SELECT card_code, 2, q2 FROM t_change_cl UNION ALL
SELECT card_code, 3, q3 FROM t_change_cl UNION ALL
SELECT card_code, 4, q4 FROM t_change_cl
ORDER BY 1, 2;

Oracle 11g+ UNPIVOT 語法

SELECT card_code, q, bal
FROM t_change_cl
UNPIVOT (bal FOR q IN (q1 AS 1, q2 AS 2, q3 AS 3, q4 AS 4))
ORDER BY card_code, q;

3. 行轉列合併字符串：多行合併為分號分隔字符串

DROP TABLE t_change_lc_comma;
CREATE TABLE t_change_lc_comma AS SELECT card_code, 'quarter_'||q AS q FROM t_change_lc;

SELECT t1.card_code,
  SUBSTR(MAX(SYS_CONNECT_BY_PATH(t1.q, ';')), 2) AS q
FROM (
  SELECT a.card_code, a.q,
    ROW_NUMBER() OVER (PARTITION BY a.card_code ORDER BY a.q) rn
  FROM t_change_lc_comma a
) t1
START WITH t1.rn = 1
CONNECT BY t1.card_code = PRIOR t1.card_code
AND t1.rn = PRIOR t1.rn + 1
GROUP BY t1.card_code;

原理：SYS_CONNECT_BY_PATH 遞歸構建路徑字符串，MAX 取最長路徑（即全部分組行），SUBSTR(...,2) 去除首個分隔符。

Oracle 11g+ LISTAGG（推薦，性能更優）

SELECT card_code, LISTAGG(q, ';') WITHIN GROUP (ORDER BY q) AS q
FROM t_change_lc_comma
GROUP BY card_code;

4. 列轉行分割字符串：分號分隔字符串拆分為多行

DROP TABLE t_change_cl_comma;
CREATE TABLE t_change_cl_comma AS
SELECT t1.card_code, SUBSTR(MAX(SYS_CONNECT_BY_PATH(t1.q, ';')), 2) AS q
FROM (
  SELECT a.card_code, a.q,
    ROW_NUMBER() OVER (PARTITION BY a.card_code ORDER BY a.q) rn
  FROM t_change_lc_comma a
) t1
START WITH t1.rn = 1
CONNECT BY t1.card_code = PRIOR t1.card_code
AND t1.rn = PRIOR t1.rn + 1
GROUP BY t1.card_code;

-- 分割查詢
SELECT card_code,
  REGEXP_SUBSTR(q, '[^;]+', 1, LEVEL) AS quarter
FROM t_change_cl_comma
CONNECT BY REGEXP_SUBSTR(q, '[^;]+', 1, LEVEL) IS NOT NULL
  AND PRIOR card_code = card_code
  AND PRIOR SYS_GUID() IS NOT NULL;

現代寫法（Oracle 12c+）：使用 JSON_TABLE 或 XMLTABLE 分割，性能與可讀性更佳。

總結與版本選擇建議

根據數據庫版本選擇適當語法，現代語法不僅代碼更簡潔，執行計劃也經過優化，性能通常優於傳統寫法。