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, YUGABYTE
/* UNSUPPORTED */

(These are currently generated with jOOQ 3.16, see #10141), or translate your own on our website

The jOOQ User Manual : SQL building : Column expressions : Aggregate functions : LISTAGG

previous : next