Building Comma Separated Values with Oracle & SQL

From time to time I’m asked to aggregate strings from multiple records into a single column using SQL. Here’s an example, showing a comma separated list of ordered employee names per department based on the famous EMP and DEPT tables.

DEPTNODNAMEENAME_LIST
10ACCOUNTINGCLARK, KING, MILLER
20RESEARCHADAMS, FORD, JONES, SCOTT, SMITH
30SALESALLEN, BLAKE, JAMES, MARTIN, TURNER, WARD
40OPERATIONS

Oracle introduced the aggregate function LISTAGG for that purpose in 11.2. If you may use LISTAGG go for it, but if you have to work with an older version of the Oracle Database Server you might be interested in some other options which I discuss per Oracle Database version.

I cover just some options in this post, if you are interested in more than please visit Tim Hall’s String Aggregation Techniques on oracle-base.com.

Oracle7

More than twenty years ago PL/SQL was introduced as part of the Oracle Database Server version 7.0 allowing to write functions to be used in SQL statements. Back than something like the following was necessary to build comma separated values:

Oracle8

Version 8.0 came with the object option allowing to solve the problem in more generic ways.

Another option was to use a REF CURSOR instead of a collection type. The PL/SQL part was executable in Oracle7 too, but the CURSOR expression was not available back than. BTW: SYS_REFCURSOR was introduced in 9.0, so this specific PL/SQL type is really necessary with version 8.0.

Oracle9i Release 1

Version 9.0 came with basic XML support which allowed to aggregate strings without the need of a helper function.

In this solution the sort order of the aggregated strings is not definable. Version 9i introduced also user-defined aggregate functions. To use them you need to implement the ODCIAggregate interface which allows you also to sort the result.

Oracle9i Release 2

Version 9i Release 2 came with SQL/XML support and the function XMLAGG which replaces SYS_XMLAGG and allows to sort the elements to be aggregated (see line 7).

One may argue that using RTRIM to get rid of the last comma is not the way to interact with XML, especially since SQL/XML supports XSLT. But probably no one can deny that writing the appropriate stylesheet is a bit more complex and time-consuming. Nonetheless, here’s a XSLT example:

Oracle Database 11g Release 2

As mentioned at the beginning, in version 11g Release 2 Oracle finally introduced the aggregate function LISTAGG  to conveniently aggregate strings.

Performance Comparison

To compare the runtime performance of the different solution approaches I created 1 million rows in the dept table and 5 million rows in the emp table using this script and measured the second serial execution of a CREATE TABLE AS SELECT statement for each of the 8 described approaches against my 11.2.0.3 instance. The following figure summarizes the results.

RuntimeCSV

Conclusion

A lot of things have changed in the Oracle Database area since Oracle7, even in the niche of string aggregation. I recommend to use LISTAGG (8) whenever possible and avoid the use of SYS_XMLAGG (4) or XSLT (7) for string aggregation. The Collection Type (2) approach is a good alternative if you do not mind creating helper objects otherwise use XMLAGG (6).

2 Comments

  1. blank Jose Pla says:

    Thanks, this made my day, not only did it solve the request I had, but also I loved trying all the different methods of achieving the same result.

  2. blank Haji Syed Hassan says:

    If you are looking to aggregate data more then 4000 characters then see below link.

    ListAgg_CLOB Custom Function

    http://sql-plsql-de.blogspot.com.au/2014/01/sql-listagg-mit-clob-ausgabe-kein.html

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.