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
This documentation is for the unreleased development version of jOOQ. Click on the above version links to get this documentation for a supported version of jOOQ.
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 | +------------+--------------+
See LISTAGG (binary) for a binary version of this function.
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 */
Generated with jOOQ 3.20. Translate your own SQL on our website
Feedback
Do you have any feedback about this page? We'd love to hear it!