的Oracle SQL LISTAGG [重复](oracle sql listagg [dupli

这个问题是完全相同的副本：

• 不能使用LISTAGG 1个回答

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

错误： - ORA-00923：FROM关键字未找到预期00923. 00000 - “FROM关键字未找到预期” *原因：
*操作：在错误行：1列：42

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

Answer 1:

对于10gR2或11gR1的Oracle版本中，你可以使用分层查询用的贡献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

文章来源: oracle sql listagg [duplicate]