Displaying multiple records in one row

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

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.

© William Robertson 2004 www.williamrobertson.net


2: I came up with this method in late 2004 when I realised that SYS_CONNECT_BY_PATH almost does the job of a character-separated string aggregate, if you can just give it a fake hierarchy to work with and take the last value in each group.

Later on, Adrian Billington pointed out to me that Mikito Harakiri had posted something very similar two years earlier, curse him, halfway down the AskTom thread String Concatenation, under the title "analytic sql solutions are disgusting". Tom "Analytics Rock" Kyte understandably took this to mean "Analytics Suck, Why Is Oracle So Dumb?" and went on to demonstrate how inefficient and ugly the SYS_CONNECT_BY_PATH + Fake Hierarchy + take the last one solution is. Which of course it is, although I think Mikito was probably just saying it's possible using analytics, but it's not pretty.
2: Simplified following posts by Tom Kyte and Donat Callen on this thread on AskTom.
3: This example is based on an OTN forum post by Michael S.