listagg

语法:
listagg(column_name[,split_character]) within group(order by column_name,...) [over(partition by column_name,...)]


SQL> select listagg(ename) within group(order by rowid) as ename from emp ;
ENAME
--------------------------------------------------------------------------------
SMITHALLENWARDJONESMARTINBLAKECLARKSCOTTKINGTURNERADAMSJAMESFORDMILLER


SQL> select listagg(ename,',') within group(order by rowid) as ename from emp ;
ENAME
--------------------------------------------------------------------------------
SMITH,ALLEN,WARD,JONES,MARTIN,BLAKE,CLARK,SCOTT,KING,TURNER,ADAMS,JAMES,FORD,MIL


SQL> select deptno,listagg(ename,',') within group(order by rowid) as ename from emp
  2  group by deptno ;
DEPTNO ENAME
------ --------------------------------------------------------------------------------
    10 CLARK,KING,MILLER
    20 SMITH,JONES,SCOTT,ADAMS,FORD
    30 ALLEN,WARD,MARTIN,BLAKE,TURNER,JAMES


SQL> select deptno,ename,listagg(ename,',') within group(order by rowid) over(partition by deptno) as ename from emp
  2  /
DEPTNO ENAME      ENAME
------ ---------- --------------------------------------------------------------------------------
    10 CLARK      CLARK,KING,MILLER
    10 KING       CLARK,KING,MILLER
    10 MILLER     CLARK,KING,MILLER
    20 SMITH      SMITH,JONES,SCOTT,ADAMS,FORD
    20 JONES      SMITH,JONES,SCOTT,ADAMS,FORD
    20 SCOTT      SMITH,JONES,SCOTT,ADAMS,FORD
    20 ADAMS      SMITH,JONES,SCOTT,ADAMS,FORD
    20 FORD       SMITH,JONES,SCOTT,ADAMS,FORD
    30 ALLEN      ALLEN,WARD,MARTIN,BLAKE,TURNER,JAMES
    30 WARD       ALLEN,WARD,MARTIN,BLAKE,TURNER,JAMES
    30 MARTIN     ALLEN,WARD,MARTIN,BLAKE,TURNER,JAMES
    30 BLAKE      ALLEN,WARD,MARTIN,BLAKE,TURNER,JAMES
    30 TURNER     ALLEN,WARD,MARTIN,BLAKE,TURNER,JAMES
    30 JAMES      ALLEN,WARD,MARTIN,BLAKE,TURNER,JAMES
14 rows selected


SQL>