Oracle 進階 SQL：LISTAGG、WM_CONCAT 與 COLLECT 實現字串彙總 (行轉列) 完全指南

Oracle 進階 SQL：LISTAGG、WM_CONCAT 與 COLLECT 實現字串彙總 (行轉列) 完全指南

「將多列資料合併為單一字串」是報表、ETL、API 回傳常見需求。Oracle 11gR2 引入 LISTAGG 之前，開發者需依賴 WM_CONCAT (非文件化)、COLLECT + 自訂函數 或 XML 技巧。本文系統比較三種主流做法，並示範分析函數寫法。

範例資料

CREATE TABLE onepiece (no NUMBER, emp VARCHAR2(20));
INSERT INTO onepiece VALUES (1001, 'lufei');
INSERT INTO onepiece VALUES (1001, 'shanzhi');
INSERT INTO onepiece VALUES (1001, 'namei');
INSERT INTO onepiece VALUES (1001, 'qiaoba');
COMMIT;
/* 目標輸出
NO   VALS
1001 lufei,shanzhi,namei,qiaoba */

方案 1：LISTAGG (11gR2+ 推薦標準做法)

SELECT no,
       LISTAGG(emp, ',') WITHIN GROUP (ORDER BY emp) AS vals
FROM   onepiece
GROUP BY no;

語法結構：
LISTAGG( measure_expr [, delimiter] ) WITHIN GROUP (ORDER BY order_by_clause) [OVER (PARTITION BY ...)]

• measure_expr：要彙總的欄位/運算式 (必填)
• delimiter：分隔符號，預設無 (必填建議給 ',')
• WITHIN GROUP (ORDER BY ...)：必填，指定彙總順序
• OVER (PARTITION BY ...)：可選，轉為分析函數，保留明細列同時顯示彙總值

分析函數寫法：不分組、保留原始列數

SELECT no,
       emp,
       LISTAGG(emp, ',') WITHIN GROUP (ORDER BY emp) OVER (PARTITION BY no) AS vals
FROM   onepiece;
/* 輸出 4 列，每列 vals 皆為完整彙總字串 */

方案 2：WM_CONCAT (10g/11g 非文件化內建)

SELECT no, WM_CONCAT(emp) AS vals
FROM   onepiece
GROUP BY no;

⚠ 警告：
① WM_CONCAT 屬於 WMSYS 內部套件，未公開文件化，升級可能失效。
② 12c 起預設不再安裝 WMSYS，需手動執行 @?/rdbms/admin/owminst.plb。
③ 無法指定排序、分隔符固定為逗號。
結論：新專案請勿使用，既有系統規劃遷移至 LISTAGG。

方案 3：COLLECT + 自訂 TO_STRING (10g+ 通用、可高度客製)

COLLECT 將欄位值聚合為嵌套表 (Nested Table)，需搭配型別轉換與字串化函數：

-- 1. 建立集合型別 (一次即可)
CREATE OR REPLACE TYPE varchar2_ntt AS TABLE OF VARCHAR2(4000);

-- 2. 字串化函數 (可自訂分隔符、排序、去重等)
CREATE OR REPLACE FUNCTION to_string(
    nt_in IN varchar2_ntt,
    delimiter_in IN VARCHAR2 DEFAULT ','
) RETURN VARCHAR2 IS
    v_idx   PLS_INTEGER;
    v_str   VARCHAR2(32767);
    v_dlm   VARCHAR2(10);
BEGIN
    v_idx := nt_in.FIRST;
    WHILE v_idx IS NOT NULL LOOP
        v_str := v_str || v_dlm || nt_in(v_idx);
        v_dlm := delimiter_in;
        v_idx := nt_in.NEXT(v_idx);
    END LOOP;
    RETURN v_str;
END to_string;
/

-- 3. 實際查詢
SELECT no,
       TO_STRING(CAST(COLLECT(emp ORDER BY emp) AS varchar2_ntt)) AS vals
FROM   onepiece
GROUP BY no;

優點：可在 TO_STRING 內實作去重、截長補短、HTML 轉義等複雜邏輯；缺點：需維護物件型別與函數，部署較繁瑣。

效能與限制比較

溢位處理最佳實踐 (12c+)

啟用 MAX_STRING_SIZE = EXTENDED 後，LISTAGG 可回傳 32767 字元。若仍可能超過，建議：

1. 改用 CLOB 版本：
   LISTAGG(TO_CLOB(emp), ',') ... (19c+ 原生支援 ON OVERFLOW TRUNCATE / ON OVERFLOW ERROR)
2. 或在應用層分頁彙總、或存入 CLOB 欄位供後續處理。

反向操作預告：字串轉列 (列轉行)

本文專注「行→列」；反向「列→行」常用 REGEXP_SUBSTR + CONNECT BY LEVEL、JSON_TABLE (12c+)、XMLTABLE 或 APEX_STRING.SPLIT (18c+)。將於下一專題詳細說明。

總結：11gR2 以上環境請直接使用 LISTAGG，語法標準、效能佳、支援分析函數；舊版環境若無法升級，COLLECT + 自訂函數 是最穩健的替代方案。