Available in versions: Dev (3.19) | Latest (3.18) | 3.17 | 3.16 | 3.15 | 3.14 | 3.13 | 3.12 | 3.11 | 3.10 | 3.9
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) -- TRINO listagg(CAST(BOOK.AUTHOR_ID AS varchar), ',') WITHIN GROUP (ORDER BY BOOK.ID) -- ACCESS, ASE, DERBY, DUCKDB, FIREBIRD, INFORMIX, MEMSQL, SNOWFLAKE, SQLDATAWAREHOUSE, SQLITE, VERTICA, YUGABYTEDB /* UNSUPPORTED */
(These are currently generated with jOOQ 3.19, see #10141), or translate your own on our website
Feedback
Do you have any feedback about this page? We'd love to hear it!