LISTAGG, WM_CONCAT

LISTAGG Analystic Function in 11g Release 2


The LISTAGG analytic function was introduced in Oracle 11g Release 2, making it very easy to aggregate strings. The nice thing about this function is it also allows us to order the elements in the concatenated list.




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



3 rows selected.

WM_CONCAT Built-in Function

If you are not running 11g Release 2, but are running a version of the database where the WM_CONCAT function is present, then it is a zero effort solution as it performs the aggregation for you.




SELECT deptno, wm_concat(ename) AS employees

FROM emp

GROUP BY deptno;



DEPTNO EMPLOYEES

---------- --------------------------------------------------

10 CLARK,KING,MILLER

20 SMITH,FORD,ADAMS,SCOTT,JONES

30 ALLEN,BLAKE,MARTIN,TURNER,JAMES,WARD

3 rows selected.