LISTAGG for Converting Rows to Comma-Separated String

In an earlier post, we saw how Oracle SQL can be used to transpose data from rows to columns.

It is a similar, though simpler, exercise to transpose data from rows to comma-separated list, using Oracle’s built-in function LISTAGG.

LISTAGG lets you concatenate multiple rows of data into a single delimiter-separated string. LISTAGG was introduced in Oracle 11G R2, before which one would use the circuitous MAX(SYS_CONNECT_BY_PATH) or STRAGG methods for the same result.

Here’s how LISTAGG works.

Using the standard departments and employees tables of HR schema: list the employees in a comma-separated list against each department they belong to.

This what the tables look like:

SQL> desc departments
 Name              Null?    Type
 ----------------- -------- ------------
 DEPARTMENT_ID     NOT NULL NUMBER(4)
 DEPARTMENT_NAME   NOT NULL VARCHAR2(30)
 MANAGER_ID                 NUMBER(6)
 LOCATION_ID                NUMBER(4)

SQL> desc employees
 Name              Null?    Type
 ----------------- -------- ------------
 EMPLOYEE_ID       NOT NULL NUMBER(6)
 FIRST_NAME                 VARCHAR2(20)
 LAST_NAME         NOT NULL VARCHAR2(25)
 EMAIL             NOT NULL VARCHAR2(25)
 PHONE_NUMBER               VARCHAR2(20)
 HIRE_DATE         NOT NULL DATE
 JOB_ID            NOT NULL VARCHAR2(10)
 SALARY                     NUMBER(8,2)
 COMMISSION_PCT             NUMBER(2,2)
 MANAGER_ID                 NUMBER(6)
 DEPARTMENT_ID              NUMBER(4)

LISTAGG in action

The SQL:

SELECT department_id
     , LISTAGG(employee_id, ',')
         WITHIN GROUP (ORDER BY employee_id) 
         AS employees
FROM   employees
GROUP BY department_id;

When executed:

SQL> SELECT department_id
  2       , LISTAGG(employee_id, ',')
  3           WITHIN GROUP (ORDER BY employee_id)
  4           AS employees
  5  FROM   employees
  6  GROUP BY department_id;

DEPARTMENT_ID EMPLOYEES
------------- ---------------------------------------
           10 200
           20 201,202
           30 114,115,116,117,118,119
           40 203
           50 120,121,122,123,124,125,126,127,128,129

In Summary

This article shows you how to use Oracle’s built-in function LISTAGG (available version 11G R2 onwards) to concatenate multiple rows of data into a delimiter-separated string.

For further reading

A selection of articles that demonstrate how to convert data from one format/structure to another:

• How to flatten heirarchical data using a single SQL
• How to convert relational data to JSON using PL/JSON
• How to convert XML data to relational form using XMLTABLE