oracle--多行转为一行的连接手段

qp8im9k765

今天遇到将多行转为一行的一个操作，多谢oracle开发板的 wildwave 提供了比较通用的解决办法，同时也将自己搜到的这方面资料整理如下，多是用于连接列值的。
String集聚连接技术

需要将多行转换为一行，例子如下：

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

    预期输出:

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

    * LISTAGG分析函数（11g Release 2）
    * WM_CONCAT内建函数
    * 自定义函数
    * 使用Ref Cursor实现通用函数
    * 用户自定义聚集函数
    * ROW_NUMBER()和SYS_CONNECT_BY_PATH函数（Oracle 9i）
    * COLLECT函数（Oracle 10g）

LISTAGG分析函数（11g Release 2）

Oracle 11g Release 2介绍了LISTAGG 函数，使得聚集连接字符串变得很容易。并且允许使用我们指定连接串中的字段顺序。使用LISTAGG如下：

    COLUMN employees FORMAT A50

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

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

    3 rows selected.

WM_CONCAT内建函数

如果你的Oracle不是11g Release 2，但是支持WM_CONCAT函数，那么解决上面的问题同样是小菜一碟，使用WM_CONCAT 函数G如下：

    COLUMN employees FORMAT A50

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

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

    3 rows selected.

自定义函数

另一个方法是自定义一个函数解决问题。get_employees对于给定部门返回一组员工：

    CREATE OR REPLACE FUNCTION get_employees (p_deptno  in  emp.deptno%TYPE)
      RETURN VARCHAR2
    IS
      l_text  VARCHAR2(32767) := NULL;
    BEGIN
      FOR cur_rec IN (SELECT ename FROM emp WHERE deptno = p_deptno) LOOP
        l_text := l_text || ',' || cur_rec.ename;
      END LOOP;
      RETURN LTRIM(l_text, ',');
    END;
    /
    SHOW ERRORS

    COLUMN employees FORMAT A50

    SELECT deptno,
           get_employees(deptno) AS employees
    FROM   emp
    GROUP by deptno;

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

    3 rows selected.

为了改善性能减少函数调用，我们预先过滤行数。.

    COLUMN employees FORMAT A50

    SELECT e.deptno,
           get_employees(e.deptno) AS employees
    FROM   (SELECT DISTINCT deptno
            FROM   emp) e;

        DEPTNO EMPLOYEES
    ---------- --------------------------------------------------
            10 CLARK,KING,MILLER
            20 SMITH,JONES,SCOTT,ADAMS,FORD
            30 ALLEN,WARD,MARTIN,BLAKE,TURNER,JAMES
            
    3 rows selected.

使用Ref Cursor实现通用函数

另一个可替代的方法是使用游标变量写一个函数来连接行值。基本和上面一样，只是传入的是一个游标，所以使得它更通用：.

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

使用如下：

    COLUMN employees FORMAT A50

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

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

    3 rows selected.

同样的，为了减少函数调用可以预先顾虑一些行。.

    COLUMN employees FORMAT A50

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

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

    3 rows selected.

用户自定义聚集函数

如果你不想使用内置的函数，你可以自定义聚集函数：

    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
    );
    /
    SHOW ERRORS


    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;
    /
    SHOW ERRORS


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

使用如下：

    COLUMN employees FORMAT A50

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

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

    3 rows selected.

ROW_NUMBER()和SYS_CONNECT_BY_PATH函数（Oracle 9i）

使用 ROW_NUMBER() 和SYS_CONNECT_BY_PATH 实现：

    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;

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

    3 rows selected.

COLLECT函数（Oracle 10g）

使用COLLECT函数，这个需要一个关联数组：

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

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

使用如下：

    COLUMN employees FORMAT A50

    SELECT deptno,
           tab_to_string(CAST(COLLECT(ename) AS t_varchar2_tab)) AS employees
    FROM   emp
    GROUP BY deptno;
           
        DEPTNO EMPLOYEES
    ---------- --------------------------------------------------
            10 CLARK,KING,MILLER
            20 SMITH,JONES,SCOTT,ADAMS,FORD
            30 ALLEN,WARD,MARTIN,BLAKE,TURNER,JAMES
            
    3 rows selected.