oracle sql listagg

Question

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

错误：- ORA-00923: FROM关键字找不到预期的00923。00000 -“从预期中找不到的关键字”*原因： *行动:行错误:1栏: 42

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

Answer 1

对于10gR2或11gR1版本的sys_connect_by_path，可以使用分层查询和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