Creating a comma-separated list in SQL

For some reason I can never understand, one of the most-asked Oracle questions on the Web goes something like this:

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

Various solutions exist, notably variations on Tom Kyte's STRAGG (STRing AGGregate) which uses a user-defined aggregate function (this facility was added in 9i). James Padfield extended this with CONCAT_ALL, which gets around the restriction that user-defined aggregates may only have one argument, and thus allows you to specify an alternative separator character. Ordering the values takes further work.

In 10g, STRAGG appeared in the WMSYS schema (used for the Workspace Management feature) as WM_CONCAT, so you can use something like this out of the box:

SELECT wmsys.wm_concat(dname) departments FROM dept;

DEPARTMENTS
--------------------------------------------
ACCOUNTING,RESEARCH,SALES,OPERATIONS

In 10g, the new COLLECT operator in SQL makes this simpler (see Adrian Billington's 10g New Features for examples, in particular "The COLLECT function in 10g") although you will still need to write a collection-to-string conversion function (often called JOIN() in other languages, probably not a good choice of name in Oracle).

As an alternative, it is possible to use the analytic ROW_NUMBER() function to simulate a hierarchy in the ordered data, and then in an outer query use SYS_CONNECT_BY_PATH (new in 9i) to show that "hierarchy" as one line, and take the last value in each group using the handy KEEP (DENSE_RANK LAST) construction also added in?9i.1 This does not result in a particularly efficient or elegant query, but at least

• It is self-contained, as no PL/SQL functions or object types are required, and
• The results are ordered.

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

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:3

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.