Oracle 字串行轉列 (聚合) 七大技法：LISTAGG、WM_CONCAT、自訂彙總、COLLECT 等

Oracle 字串行轉列 (聚合) 七大技法：LISTAGG、WM_CONCAT、自訂彙總、COLLECT 等

需求：將多行資料聚合為單一行，以分隔符串接。以 SCOTT.EMP 為例，依部門彙總員工姓名：10: CLARK,KING,MILLER。本文介紹 7 種方法，從原生函數到自訂彙總。

基礎資料

DEPTNO ENAME
------ ------
10     CLARK
10     KING
10     MILLER
20     SMITH
20     JONES
20     SCOTT
20     ADAMS
20     FORD
30     ALLEN
30     WARD
30     MARTIN
30     BLAKE
30     TURNER
30     JAMES

1. LISTAGG (11g R2+ 推薦)

SELECT deptno,
       LISTAGG(ename, ',') WITHIN GROUP (ORDER BY ename) AS employees
FROM emp
GROUP BY deptno;

• 原生函數、效能最佳、支援 ORDER BY、可作為分析函數 (OVER (PARTITION BY ...))。

2. WM_CONCAT (11g R2 前、非官方支援)

SELECT deptno, WM_CONCAT(ename) AS employees
FROM emp
GROUP BY deptno;

• 非文件化函數，Oracle 不保證相容性 (MOS 1336219.1)，生產環境慎用。

3. 自訂彙總函數 (User-Defined Aggregate)

CREATE OR REPLACE TYPE t_string_agg AS OBJECT (
  g_string VARCHAR2(32767),
  STATIC FUNCTION ODCIAggregateInitialize(sctx IN OUT t_string_agg) RETURN NUMBER,
  MEMBER FUNCTION ODCIAggregateIterate(self IN OUT t_string_agg, value IN VARCHAR2) RETURN NUMBER,
  MEMBER FUNCTION ODCIAggregateTerminate(self IN t_string_agg, returnValue OUT VARCHAR2, flags IN NUMBER) RETURN NUMBER,
  MEMBER FUNCTION ODCIAggregateMerge(self IN OUT t_string_agg, ctx2 IN t_string_agg) RETURN NUMBER
);

CREATE OR REPLACE TYPE BODY t_string_agg IS
  STATIC FUNCTION ODCIAggregateInitialize(sctx IN OUT t_string_agg) RETURN NUMBER IS
  BEGIN sctx := t_string_agg(NULL); RETURN ODCIConst.Success; END;
  MEMBER FUNCTION ODCIAggregateIterate(self IN OUT t_string_agg, value IN VARCHAR2) RETURN NUMBER IS
  BEGIN SELF.g_string := SELF.g_string || ',' || value; RETURN ODCIConst.Success; END;
  MEMBER FUNCTION ODCIAggregateTerminate(self IN t_string_agg, returnValue OUT VARCHAR2, flags IN NUMBER) RETURN NUMBER IS
  BEGIN returnValue := RTRIM(LTRIM(SELF.g_string, ','), ','); RETURN ODCIConst.Success; END;
  MEMBER FUNCTION ODCIAggregateMerge(self IN OUT t_string_agg, ctx2 IN t_string_agg) RETURN NUMBER IS
  BEGIN SELF.g_string := SELF.g_string || ',' || ctx2.g_string; RETURN ODCIConst.Success; END;
END;

CREATE OR REPLACE FUNCTION string_agg(p_input VARCHAR2) RETURN VARCHAR2
PARALLEL_ENABLE AGGREGATE USING t_string_agg;

SELECT deptno, string_agg(ename) FROM emp GROUP BY deptno;

4. 專用函數 (Specific Function)

CREATE OR REPLACE FUNCTION get_employees(p_deptno emp.deptno%TYPE) RETURN VARCHAR2 IS
  l_text VARCHAR2(32767);
BEGIN
  FOR r IN (SELECT ename FROM emp WHERE deptno = p_deptno ORDER BY ename) LOOP
    l_text := l_text || ',' || r.ename;
  END LOOP;
  RETURN LTRIM(l_text, ',');
END;

SELECT deptno, get_employees(deptno) FROM emp GROUP BY deptno;
-- 優化：先 SELECT DISTINCT deptno 減少呼叫次數

5. 通用游標函數 (Ref Cursor)

CREATE OR REPLACE FUNCTION concatenate_list(p_cursor IN SYS_REFCURSOR) RETURN VARCHAR2 IS
  l_ret VARCHAR2(32767); l_tmp VARCHAR2(32767);
BEGIN
  LOOP FETCH p_cursor INTO l_tmp; EXIT WHEN p_cursor%NOTFOUND;
    l_ret := l_ret || ',' || l_tmp;
  END LOOP;
  RETURN LTRIM(l_ret, ',');
END;

SELECT e1.deptno,
       concatenate_list(CURSOR(SELECT e2.ename FROM emp e2 WHERE e2.deptno = e1.deptno))
FROM emp e1 GROUP BY e1.deptno;

6. ROW_NUMBER + SYS_CONNECT_BY_PATH (9i+)

SELECT deptno,
       LTRIM(MAX(SYS_CONNECT_BY_PATH(ename, ',')) KEEP (DENSE_RANK LAST ORDER BY curr), ',') AS employees
FROM (
  SELECT deptno, ename,
         ROW_NUMBER() OVER (PARTITION BY deptno ORDER BY ename) AS curr,
         ROW_NUMBER() OVER (PARTITION BY deptno ORDER BY ename) - 1 AS prev
  FROM emp
)
GROUP BY deptno
CONNECT BY prev = PRIOR curr AND deptno = PRIOR deptno
START WITH curr = 1;

7. COLLECT + 自訂轉換函數 (10g+)

CREATE OR REPLACE TYPE t_varchar2_tab AS TABLE OF VARCHAR2(4000);

CREATE OR REPLACE FUNCTION tab_to_string(p_tab IN t_varchar2_tab, p_delim IN VARCHAR2 DEFAULT ',') RETURN VARCHAR2 IS
  l_str VARCHAR2(32767);
BEGIN
  FOR i IN p_tab.FIRST .. p_tab.LAST LOOP
    IF i != p_tab.FIRST THEN l_str := l_str || p_delim; END IF;
    l_str := l_str || p_tab(i);
  END LOOP;
  RETURN l_str;
END;

SELECT deptno, tab_to_string(CAST(COLLECT(ename) AS t_varchar2_tab)) FROM emp GROUP BY deptno;

選型建議