Available in versions: Dev (3.18) | Latest (3.17) | 3.16 | 3.15 | 3.14 | 3.13 | 3.12 | 3.11 | 3.10 | 3.9 | 3.8
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, YUGABYTEDB
/* UNSUPPORTED */
(These are currently generated with jOOQ 3.18, see #10141), or translate your own on our website
Feedback
Do you have any feedback about this page? We'd love to hear it!