New versions: Dev (3.16) | Latest (3.15) | 3.14 | 3.13 | 3.12 | 3.11 | 3.10 | 3.9 | 3.8 | Old versions: 3.7 | 3.6 | 3.5 | 3.3

LISTAGG

Applies to ✅ Open Source Edition   ✅ Express Edition   ✅ Professional Edition   ✅ Enterprise Edition

The LISTAGG() aggregate function aggregates data into a string. It uses the WITHIN GROUP syntax.

SELECT
  listagg(ID) WITHIN GROUP (ORDER BY ID),
  listagg(ID, '; ') WITHIN GROUP (ORDER BY ID),
FROM BOOK
create.select(
         listagg(BOOK.ID).withinGroupOrderBy(BOOK.ID),
         listagg(BOOK.ID, "; ").withinGroupOrderBy(BOOK.ID))
      .from(BOOK).fetch();

Producing:

+------------+--------------+
| listagg    | listagg      |
+------------+--------------+
| 1, 2, 3, 4 | 1; 2; 3; 4   |
+------------+--------------+

Dialect support

This example using jOOQ:

listAgg(BOOK.AUTHOR_ID, ",").withinGroupOrderBy(BOOK.ID)

Translates to the following dialect specific expressions:

-- AURORA_MYSQL, H2, HSQLDB, MARIADB, MYSQL
group_concat(BOOK.AUTHOR_ID ORDER BY BOOK.ID SEPARATOR ',')

-- AURORA_POSTGRES, HANA, POSTGRES
string_agg(CAST(BOOK.AUTHOR_ID AS varchar), ',' ORDER BY BOOK.ID)

-- BIGQUERY, COCKROACHDB
string_agg(CAST(BOOK.AUTHOR_ID AS string), ',' ORDER BY BOOK.ID)

-- DB2, EXASOL, ORACLE, REDSHIFT
listagg(BOOK.AUTHOR_ID, ',') WITHIN GROUP (ORDER BY BOOK.ID)

-- SQLSERVER
string_agg(CAST(BOOK.AUTHOR_ID AS varchar(max)), ',') WITHIN GROUP (ORDER BY BOOK.ID)

-- SYBASE
list(CAST(BOOK.AUTHOR_ID AS varchar), ',' ORDER BY BOOK.ID)

-- TERADATA
substring(xmlserialize(content xmlagg((',' || CAST(BOOK.AUTHOR_ID AS varchar(32000))) ORDER BY BOOK.ID) as varchar(32000)) FROM 2)

-- ACCESS, ASE, DERBY, FIREBIRD, INFORMIX, MEMSQL, SNOWFLAKE, SQLDATAWAREHOUSE, SQLITE, VERTICA, YUGABYTE
/* UNSUPPORTED */

(These are currently generated with jOOQ 3.16, see #10141), or translate your own on our website

Feedback

Do you have any feedback about this page? We'd love to hear it!

The jOOQ Logo