Displaying multiple records in one row

This article was written in 2004 for Oracle 10g. Since then, 11g has added theLISTAGG 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 WARD14 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 onTom 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 youto specify an alternative separator character. Ordering the values takes further work.

In 10g, STRAGG appeared in the WMSYS schema (used for theWorkspace Managementfeature) 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 COLLECToperator in SQL makes this simpler (see Adrian Billington’s 10g New Featuresfor 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 hierarchyin 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

• 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”:1 Thanks Mathguy on StackOverflow for spotting some errors in the previous version.

select deptno     , ltrim(sys_connect_by_path(ename,','),',') as stafffrom   ( select deptno              , ename              , row_number() over (partition by deptno order by ename) as seq         from   emp )where  connect_by_isleaf = 1connect by seq = prior seq +1 and deptno = prior deptnostart with seq = 1;    DEPTNO STAFF---------- --------------------------------------------------        10 CLARK,KING,MILLER        20 ADAMS,FORD,JONES,SCOTT,SMITH        30 ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD3 rows selected.

Another approach involves harnessing the dark power of XML:

select deptno     , rtrim       ( xmlagg(xmlelement(c, ename || ',') order by ename).extract ('//text()')       , ',' ) as stafffrom   empgroup by deptno;    DEPTNO STAFF---------- ---------------------------------------------------------------        10 CLARK,KING,MILLER        20 ADAMS,FORD,JONES,SCOTT,SMITH        30 ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD3 rows selected.

From 11g, of course, you can useLISTAGG:

select deptno     , listagg(ename,',') within group(order by ename) as stafffrom   empgroup by deptno;