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
Applies to ✅ 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
group_concat(BOOK.ID SEPARATOR ',')
Aurora Postgres, DuckDB, Hana, Postgres
string_agg(CAST(BOOK.ID AS varchar), ',')
BigQuery, CockroachDB
string_agg(CAST(BOOK.ID AS string), ',')
DB2, Exasol, Redshift
listagg(BOOK.ID, ',')
Oracle
listagg(BOOK.ID, ',') WITHIN GROUP (ORDER BY NULL)
SQLite
group_concat(BOOK.ID, ',')
SQLServer
string_agg(CAST(BOOK.ID AS varchar(max)), ',')
Sybase
list(CAST(BOOK.ID AS varchar), ',')
Teradata
substring(xmlserialize(CONTENT xmlagg((',' || CAST(BOOK.ID AS varchar(32000)))) AS varchar(32000)) FROM 2)
Trino
listagg(CAST(BOOK.ID AS varchar), ',') WITHIN GROUP (ORDER BY NULL)
ASE, Access, ClickHouse, Databricks, Derby, 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!