Available in versions: Dev (3.21) | Latest (3.20) | 3.19 | 3.18 | 3.17 | 3.16 | 3.15 | 3.14 | 3.13 | 3.12 | 3.11
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, Databricks, Derby, Firebird, Informix, MemSQL, SQLDataWarehouse, SQLite, Snowflake, Vertica, YugabyteDB
/* UNSUPPORTED */
Generated with jOOQ 3.21. Support in older jOOQ versions may differ. Translate your own SQL on our website
Feedback
Do you have any feedback about this page? We'd love to hear it!