Oracle 分組後顯示組內其他列：WM_CONCAT / LISTAGG 聚合函數

需求

SELECT NAME FROM EMP GROUP BY NAME 僅返回分組列，如何同時顯示每組內的 ID 列表？

解決方案

1. Oracle 11gR2+：LISTAGG（標準、推薦）

SELECT NAME, LISTAGG(ID, ',') WITHIN GROUP (ORDER BY ID) AS ids
FROM EMP
GROUP BY NAME;

• 支援排序、分隔符自定義、無長度限制（超過 4000 需 ON OVERFLOW TRUNCATE）。

2. Oracle 10g/11g：WM_CONCAT（非官方、隱含）

SELECT NAME, WM_CONCAT(ID) AS ids
FROM EMP
GROUP BY NAME;

• 不支援 ORDER BY、返回 CLOB 需轉換、未來版本可能移除。

3. 通用方案：SYS_CONNECT_BY_PATH + 層次查詢（兼容所有版本）

SELECT NAME, 
       LTRIM(MAX(SYS_CONNECT_BY_PATH(ID, ',')), ',') AS ids
FROM (
    SELECT NAME, ID,
           ROW_NUMBER() OVER (PARTITION BY NAME ORDER BY ID) AS rn,
           COUNT(*) OVER (PARTITION BY NAME) AS cnt
    FROM EMP
)
WHERE rn = cnt
GROUP BY NAME
CONNECT BY PRIOR NAME = NAME AND PRIOR rn = rn - 1
START WITH rn = 1;

選擇建議

總結

Oracle 分組顯示組內其他列，首選 LISTAGG**（現代版本），舊版本可用 WM_CONCAT 但需規劃遷移。複雜排序/去重場景直接用 LISTAGG 參數即可滿足。