LISTAGG - Concatenate multiple rows into a single delimiter-separated string

It is a similar, though simpler, exercise to transpose data from rows to a comma-seperated list.

LISTAGG is a in-built function in Oracle that 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.

The SQL:

SELECT deptno
     , LISTAGG(empno, ',')
         WITHIN GROUP (ORDER BY empno)
         AS emp
FROM   emp
GROUP BY deptno;
When executed:

SQL> SELECT deptno
  2       , LISTAGG(empno, ',')
  3           WITHIN GROUP (ORDER BY empno)
  4           AS emp
  5  FROM   emp
  6  GROUP BY deptno;

deptno emp
------------- ---------------------------------------
           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