SQL实现多行记录聚合成一行

将一个字段的多行记录按照另一个字段值进行分组，组合成一行记录。新组合成的一行记录可以使用特定字符如逗号加以区分。

在以前的文档中，我写好一个方法( http://mikixiyou.iteye.com/blog/1489127 )。使用row_number() over 函数。那是在Oracle 10g中使用到的，在11g中提供一个新函数listagg来实现该功能，简单易用。

 

在Oracle 10g中，如果将scott.emp中字段deptno相同的不同ename的记录，从多行记录聚合转换成一行。这是需要自己开发才能完成的。
scott.emp表中原始记录如下：
   DEPTNO ENAME
--------- ----------
       10 CLARK
       10 KING
       10 MILLER
       20 ADAMS
       20 FORD
       20 JONES
      
聚合后的结果如下：

   DEPTNO AGGREGATED_ENAMES
--------- -------------------------
       10 CLARK,KING,MILLER
       20 ADAMS,FORD,JONES

自己开发实现的程序如下：      

 

select deptno, substr(max(sys_connect_by_path(ename, ',')), 2) as employees
  from (select deptno,
               ename,
               row_number() over(partition by deptno order by ename) as rown
          from emp)
 start with rown = 1
connect by prior deptno = deptno
       and prior rown = rown - 1
 group by deptno;

 

 

(miki西游  @mikixiyou 原文链接: http://mikixiyou.iteye.com/blog/1627981 )

在11g中，提供了一个新函数 listagg，可以实现这种功能。

listagg的语法如下：

LISTAGG( [,]) WITHIN GROUP (ORDER BY ) [OVER (PARTITION BY )]

WITHIN GROUP 是关键字，必须加。
ORDER BY 也是关键字，必须加。
OVER (PARTITION BY)是可选子句，加上能将聚合函数变成分析函数。

实现该功能的程序如下：

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

 

 
使用over子句将该函数变成分析函数。

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

 

将每个员工的信息都列出来，最后一项是该员工所在部门所有员工的员工排列，并且是按照雇佣时间排列的。

得到的结果如下：

     DEPTNO ENAME      HIREDATE    EMPLOYEES
--------- ---------- ----------- -------------------------------------
        10 CLARK      09/06/1981  CLARK,KING,MILLER
        10 KING       17/11/1981  CLARK,KING,MILLER
        10 MILLER     23/01/1982  CLARK,KING,MILLER
        20 SMITH      17/12/1980  SMITH,JONES,FORD,SCOTT,ADAMS
        20 JONES      02/04/1981  SMITH,JONES,FORD,SCOTT,ADAMS
        20 FORD       03/12/1981  SMITH,JONES,FORD,SCOTT,ADAMS
        20 SCOTT      19/04/1987  SMITH,JONES,FORD,SCOTT,ADAMS
        20 ADAMS      23/05/1987  SMITH,JONES,FORD,SCOTT,ADAMS
        30 ALLEN      20/02/1981  ALLEN,WARD,BLAKE,TURNER,MARTIN,JAMES
        30 WARD       22/02/1981  ALLEN,WARD,BLAKE,TURNER,MARTIN,JAMES
        30 BLAKE      01/05/1981  ALLEN,WARD,BLAKE,TURNER,MARTIN,JAMES
        30 TURNER     08/09/1981  ALLEN,WARD,BLAKE,TURNER,MARTIN,JAMES
        30 MARTIN     28/09/1981  ALLEN,WARD,BLAKE,TURNER,MARTIN,JAMES
        30 JAMES      03/12/1981  ALLEN,WARD,BLAKE,TURNER,MARTIN,JAMES