DanBrother的部落格

Oracle LISTAGG Analystic Function (11gR2)
Purpose: To aggregate data from a number of rows into a single row.

SQL> desc emp
名稱 空值? 類型
------------------------------------------ -------- -----------------
EMPNO NOT NULL NUMBER(4)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2)

SQL> select deptno,ename
from emp
order by deptno;

>>
DEPTNO ENAME
---------- --------------------
10 CLARK
10 MILLER
10 KING
20 FORD
20 SCOTT
20 JONES
20 SMITH
20 ADAMS
30 WARD
30 MARTIN
30 TURNER
30 JAMES
30 ALLEN
30 BLAKE

col employees format a60
select deptno,LISTAGG(ename,',') within group (order by ename) as employees
from emp
group by deptno;
>>
DEPTNO EMPLOYEES
---------- ------------------------------------------------------------
10 CLARK,KING,MILLER
20 ADAMS,FORD,JONES,SCOTT,SMITH
30 ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD

col employees format a60
select deptno,listagg(ename,' / ') within group (order by ename) as employees
from emp
group by deptno;

>> DEPTNO EMPLOYEES
---------- ------------------------------------------------------------
10 CLARK / KING / MILLER
20 ADAMS / FORD / JONES / SCOTT / SMITH
30 ALLEN / BLAKE / JAMES / MARTIN / TURNER / WARD