Displaying multiple records in one row

This article was written in 2004 for Oracle 10g. Since then, 11g has added the LISTAGG function, which provides all the list aggregation functionality a person could want. In addition to the manual, there's a great article about ways to use it on oracle-developer.net.

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 if it's installed in your environment (it's an optional feature) 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 to show that "hierarchy" as one line, and take the last value in each group using the handy if verbose KEEP (DENSE_RANK LAST) construction available since 9i. 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":1 Thanks Mathguy on StackOverflow for spotting some errors in the previous version. 

select deptno
     , ltrim(sys_connect_by_path(ename,','),',') as staff
from   ( select deptno
              , ename
              , row_number() over (partition by deptno order by ename) as seq
         from   emp )
where  connect_by_isleaf = 1
connect by seq = prior seq +1 and deptno = prior deptno
start with seq = 1;


    DEPTNO STAFF
---------- --------------------------------------------------
        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 staff
from   emp
group by deptno;


    DEPTNO STAFF
---------- ---------------------------------------------------------------
        10 CLARK,KING,MILLER
        20 ADAMS,FORD,JONES,SCOTT,SMITH
        30 ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD

3 rows selected.

From 11g, of course, you can use LISTAGG:

select deptno
     , listagg(ename,',') within group(order by ename) as staff
from   emp
group by deptno;