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
GROUP_CONCAT
Supported by ✅ Open Source Edition ✅ Express Edition ✅ Professional Edition ✅ Enterprise Edition
The GROUP_CONCAT() aggregate function is the MySQL version of the standard SQL LISTAGG function, to concatenate aggregate data into a string. It supports being used with an ORDER BY clause, which uses the expected syntax, unlike LISTAGG(), which uses the WITHIN GROUP syntax.
SELECT group_concat(ID), group_concat(ID ORDER BY ID), group_concat(ID SEPARATOR '; '), group_concat(ID ORDER BY ID SEPARATOR '; '), FROM BOOK
create.select(
groupConcat(BOOK.ID),
groupConcat(BOOK.ID).orderBy(BOOK.ID),
groupConcat(BOOK.ID).separator("; "),
groupConcat(BOOK.ID).orderBy(BOOK.ID).separator("; "))
.from(BOOK).fetch();
Producing:
+--------------+--------------+--------------+--------------+ | group_concat | group_concat | group_concat | group_concat | +--------------+--------------+--------------+--------------+ | 1, 3, 4, 2 | 1, 2, 3, 4 | 1; 3; 4; 2 | 1; 2; 3; 4 | +--------------+--------------+--------------+--------------+
Dialect support
This example using jOOQ:
groupConcat(BOOK.ID)
Translates to the following dialect specific expressions:
Aurora MySQL, H2, HSQLDB, MariaDB, MemSQL, MySQL, SQLite
group_concat(BOOK.ID)
Aurora Postgres, DuckDB, Hana, Postgres
string_agg(CAST(BOOK.ID AS varchar), '')
BigQuery, CockroachDB, Spanner
string_agg(CAST(BOOK.ID AS string), '')
DB2, Exasol, Oracle, Redshift
listagg(BOOK.ID)
SQLServer
string_agg(CAST(BOOK.ID AS varchar(max)), '')
Sybase
list(CAST(BOOK.ID AS varchar), '')
Teradata
xmlserialize(CONTENT xmlagg(BOOK.ID) AS varchar(32000))
Trino
listagg(CAST(BOOK.ID AS varchar))
ASE, Access, ClickHouse, Databricks, Firebird, Informix, SQLDataWarehouse, 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!