Displaying multiple records in one row

I have a table with values as follows:

SQL> SELECT deptno, ename FROM emp ORDER BY deptno, ename;

DEPTNO ENAME

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

10 CLARK

10 KING

10 MILLER

20 ADAMS

20 FORD

20 JONES

20 SCOTT

20 SMITH

30 ALLEN

30 BLAKE

30 JAMES

30 MARTIN

30 TURNER

30 WARD

14 rows selected.

but I need them in the following less convenient format:

DEPTNO ENAME

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

10 CLARK, KING, MILLER

20 ADAMS, FORD, JONES, SCOTT, SMITH

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

The following example illustrates the technique using the SCOTT demo table "emp":²

SELECT deptno

, LTRIM(SYS_CONNECT_BY_PATH(ename,','))

FROM ( SELECT deptno, ename

, ROW_NUMBER() OVER (PARTITION BY deptno ORDER BY ename) -1 AS seq

FROM emp )

WHERE connect_by_isleaf = 1

CONNECT BY seq = PRIOR seq +1 AND deptno = PRIOR deptno

START WITH seq = 1;

DEPTNO CONCATENATED

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

10 CLARK,KING,MILLER

20 ADAMS,FORD,JONES,SCOTT,SMITH

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

3 rows selected.

Another approach involves harnessing the dark power of XML:³

SELECT deptno

, RTRIM

( xmlagg (xmlelement (c, ename || ',') order by ename).extract ('//text()')

, ',' ) AS concatenated

FROM emp

GROUP BY deptno;

DEPTNO CONCATENATED

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

10 CLARK,KING,MILLER

20 ADAMS,FORD,JONES,SCOTT,SMITH

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

3 rows selected.