语法:
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>
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>