listagg()
1、普通函数
SQL> select listagg(ename,',')within group(order by sal)name from scott.emp;
NAME
----------------------------------------------------------------------------------------------------
SMITH,JAMES,ADAMS,MARTIN,WARD,MILLER,TURNER,ALLEN,CLARK,BLAKE,JONES,FORD,SCOTT,KING
2、分组函数
SQL> select deptno,listagg(ename,',')within group(order by sal)name from scott.
emp group by deptno;
DEPTNO NAME
---------- ----------------------------------------------------------------------------------------------------
MILLER,CLARK,KING
SMITH,ADAMS,JONES,FORD,SCOTT
JAMES,MARTIN,WARD,TURNER,ALLEN,BLAKE
3、分析函数
SQL> select deptno,ename,sal,listagg(ename,',')within group(order by sal)over(partition by deptno)name from scott.
emp;
DEPTNO ENAME SAL NAME
---------- ---------- ---------- ----------------------------------------
MILLER 1300 MILLER,CLARK,KING
CLARK 2450 MILLER,CLARK,KING
KING 5000 MILLER,CLARK,KING
SMITH 800 SMITH,ADAMS,JONES,SCOTT,FORD
ADAMS 1100 SMITH,ADAMS,JONES,SCOTT,FORD
JONES 2975 SMITH,ADAMS,JONES,SCOTT,FORD
SCOTT 3000 SMITH,ADAMS,JONES,SCOTT,FORD
FORD 3000 SMITH,ADAMS,JONES,SCOTT,FORD
JAMES 950 JAMES,MARTIN,WARD,TURNER,ALLEN,BLAKE
MARTIN 1250 JAMES,MARTIN,WARD,TURNER,ALLEN,BLAKE
WARD 1250 JAMES,MARTIN,WARD,TURNER,ALLEN,BLAKE
TURNER 1500 JAMES,MARTIN,WARD,TURNER,ALLEN,BLAKE
ALLEN 1600 JAMES,MARTIN,WARD,TURNER,ALLEN,BLAKE
BLAKE 2850 JAMES,MARTIN,WARD,TURNER,ALLEN,BLAKE
rows selected.