甲骨文sql listagg

本文介绍了甲骨文sql listagg的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

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

Oracle数据库11g企业版11.1.0.7.0版-64位生产

Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production

推荐答案

对于Oracle的10gR2或11gR1版本，可以在sys_connect_by_path的作用下使用层次查询:

For the 10gR2 or 11gR1 versions of Oracle , you can use hierarchical queries with the contribution of sys_connect_by_path :

with emp( ename, deptno ) as
(
 select 'CLARK',10 from dual union all
 select 'MILLER',10 from dual union all
 select 'KING',10 from dual union all
 select 'FORD',20 from dual union all
 select 'SCOTT',20 from dual union all
 select 'JONES',20 from dual union all
 select 'SMITH',20 from dual union all
 select 'ADAMS',20 from dual union all
 select 'WARD',30 from dual union all
 select 'MARTIN',30 from dual union all
 select 'TURNER',30 from dual union all
 select 'JAMES',30 from dual union all
 select 'ALLEN',30 from dual union all
 select 'BLAKE',30 from dual
)
select deptno, ltrim(sys_connect_by_path(ename, ','), ',') as enames
  from (select deptno,
               ename,
               row_number() over(partition by deptno order by ename) as rn
          from emp)
 where connect_by_isleaf = 1
connect by deptno = prior deptno
       and rn = prior rn + 1
 start with rn = 1;

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

这篇关于甲骨文sql listagg的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！