LISTAGG Function

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,SMITH
    30   ALLEN,ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD,WARD