Available in versions: Dev (3.20) | Latest (3.19) | 3.18 | 3.17 | 3.16 | 3.15 | 3.14 | 3.13 | 3.12 | 3.11 | 3.10

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, DuckDB, 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)

Trino

listagg(CAST(BOOK.AUTHOR_ID AS varchar), ',') WITHIN GROUP (ORDER BY BOOK.ID)

ASE, Access, ClickHouse, Derby, Firebird, Informix, MemSQL, SQLDataWarehouse, SQLite, Snowflake, Vertica, YugabyteDB

/* UNSUPPORTED */

(These are currently generated with jOOQ 3.20, 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