Oracle 行列轉換完全指南：WM_CONCAT、PIVOT/UNPIVOT 及多種進階寫法總結

Oracle 行列轉換完全指南：WM_CONCAT、PIVOT/UNPIVOT 及多種進階寫法總結

一、基礎字串拼接與拆分

1. 多行合併為單一字串

SELECT id || username AS str FROM app_user;
SELECT CONCAT(id, username) AS str FROM app_user;

2. 字串拆分為多欄/多行

• SUBSTR + INSTR 定位分隔符
• REGEXP_SUBSTR 正則擷取（11g+ 推薦）
• UNION ALL 手動展開

二、WM_CONCAT：經典行轉列利器（12c 前主流）

WM_CONCAT(col) 以逗號預設分隔，將群組內多行合併為單一字串。

測試資料建立

CREATE TABLE test(id NUMBER, name VARCHAR2(20));
INSERT INTO test VALUES(1,'a');
INSERT INTO test VALUES(1,'b');
INSERT INTO test VALUES(1,'c');
INSERT INTO test VALUES(2,'d');
INSERT INTO test VALUES(2,'e');

效果展示

等價傳統寫法（兼容 8i/9i/10g）

-- MAX + DECODE
SELECT id,
       MAX(DECODE(rn,1,name))||
       MAX(DECODE(rn,2,','||name))||
       MAX(DECODE(rn,3,','||name)) str
FROM (SELECT id,name,ROW_NUMBER() OVER(PARTITION BY id ORDER BY name) rn FROM test)
GROUP BY id;

-- SYS_CONNECT_BY_PATH
SELECT id, SUBSTR(SYS_CONNECT_BY_PATH(name,','),2) str
FROM (SELECT id,name,ROW_NUMBER() OVER(PARTITION BY id ORDER BY name) rn FROM test)
START WITH rn=1 CONNECT BY rn=PRIOR rn+1 AND id=PRIOR id
GROUP BY id;

懶人擴展：自動產生建檢視 SQL

SELECT 'CREATE OR REPLACE VIEW v_app AS SELECT '||
       WM_CONCAT(column_name)||' FROM app_user' sql_str
FROM user_tab_columns WHERE table_name='APP_USER';

三、Oracle 11g PIVOT / UNPIVOT 原生支援

1. PIVOT：行轉列（聚合）

測試資料：水果銷售記錄

CREATE TABLE demo(id INT, name VARCHAR2(20), nums INT);
INSERT INTO demo VALUES(1,'蘋果',1000);
INSERT INTO demo VALUES(2,'蘋果',2000);
INSERT INTO demo VALUES(3,'蘋果',4000);
INSERT INTO demo VALUES(4,'橘子',5000);
INSERT INTO demo VALUES(5,'橘子',3000);
INSERT INTO demo VALUES(6,'葡萄',3500);
INSERT INTO demo VALUES(7,'芒果',4200);
INSERT INTO demo VALUES(8,'芒果',5500);

語法：

SELECT * FROM (
  SELECT name, nums FROM demo
) PIVOT (
  SUM(nums) FOR name IN ('蘋果' AS 蘋果, '橘子' AS 橘子, '葡萄' AS 葡萄, '芒果' AS 芒果)
);

結果：單行顯示四種水果總銷量欄位。

等價傳統寫法（多子查詢聯結，效能較差）。

2. UNPIVOT：列轉行（規格化）

測試資料：寬表記錄季度銷售

CREATE TABLE fruit(id INT, name VARCHAR2(20), q1 INT, q2 INT, q3 INT, q4 INT);
INSERT INTO fruit VALUES(1,'蘋果',1000,2000,3300,5000);
INSERT INTO fruit VALUES(2,'橘子',3000,3000,3200,1500);
INSERT INTO fruit VALUES(3,'香蕉',2500,3500,2200,2500);
INSERT INTO fruit VALUES(4,'葡萄',1500,2500,1200,3500);

語法：

SELECT id, name, jidu, xiaoshou
FROM fruit
UNPIVOT (xiaoshou FOR jidu IN (q1, q2, q3, q4));

結果：每種水果拆解為 4 行，含季度與銷量欄位。

3. PIVOT XML：動態欄位值

當 IN 列表值未知時，使用 PIVOT XML ... IN (ANY) 產生 XMLType 輸出，再由 XML 解析器處理。

SELECT * FROM (
  SELECT name, nums AS "Purchase Frequency" FROM demo
) PIVOT XML (SUM(nums) FOR name IN (ANY));

四、總結建議

• 11g+ 環境：優先使用 PIVOT/UNPIVOT，語法清晰、效能優化、支援 XML 動態欄位。
• 10g/舊版本：使用 WM_CONCAT（非官方文件化）或 MAX+DECODE、SYS_CONNECT_BY_PATH 等標準寫法。
• 大數據量：注意 WM_CONCAT 可能受 4000 位元組限制，考慮 LISTAGG (11gR2+) 或自定義聚合函數。