Skip to content
Tutorials Center
My learning

Topics

SQL Reference ยท Aggregate Functions

SQL GROUP_CONCAT() and STRING_AGG()

Reference for SQL string aggregation: GROUP_CONCAT in SQLite and MySQL, STRING_AGG in PostgreSQL and SQL Server, LISTAGG in Oracle, with ordering.

String aggregation joins the values of many rows into one text value per group, for example all book titles of an author as 'A, B, C'. The standard calls it LISTAGG; in practice every database spells it differently: GROUP_CONCAT (SQLite, MySQL), STRING_AGG (PostgreSQL, SQL Server) and LISTAGG (Oracle).

NULL values are skipped. If the group has no non-NULL values the result is NULL.

SQL
SELECT author_id, GROUP_CONCAT(title, '; ') AS titles
FROM books
GROUP BY author_id;

Try it

SQL Titles per author ยท database: bookstore

Press Run (or Ctrl + Enter) to see the result here.

SQL Controlling the order with a subquery ยท database: bookstore

Press Run (or Ctrl + Enter) to see the result here.

SQL DISTINCT values and NULLs (36 rows, 33 emails) ยท database: company

Press Run (or Ctrl + Enter) to see the result here.

Syntax

SQL
-- SQLite
GROUP_CONCAT([DISTINCT] expr [, separator])

-- MySQL
GROUP_CONCAT([DISTINCT] expr [ORDER BY ...] [SEPARATOR 'text'])

-- PostgreSQL, SQL Server
STRING_AGG(expr, separator [ORDER BY ...])

-- Oracle, standard SQL
LISTAGG(expr, separator) WITHIN GROUP (ORDER BY ...)
Part Description
expr The value to join. It is converted to text. NULLs are skipped.
separator Text placed between values. SQLite defaults to a comma without a space.
ORDER BY Where supported, the order in which values are joined.

Returns: one text value per group, or NULL if there is nothing to join.

Dialect differences

Database Function Ordering Notes
SQLite GROUP_CONCAT(x, sep) ORDER BY inside the call from 3.44; older versions give no guarantee string_agg(x, sep) is an alias from 3.44. DISTINCT allows only the default separator.
PostgreSQL STRING_AGG(x, sep ORDER BY y) Inside the call Separator is required. The expression must be text, so cast numbers.
MySQL GROUP_CONCAT(x ORDER BY y SEPARATOR ', ') Inside the call Output is cut at group_concat_max_len (1024 bytes by default).
SQL Server STRING_AGG(x, sep) WITHIN GROUP (ORDER BY y) WITHIN GROUP Available from SQL Server 2017.
Oracle LISTAGG(x, sep) WITHIN GROUP (ORDER BY y) Required WITHIN GROUP Fails with ORA-01489 if the result exceeds the length limit; 19c+ offers ON OVERFLOW TRUNCATE.
SQL
-- PostgreSQL
SELECT author_id, STRING_AGG(title, ', ' ORDER BY title) AS titles
FROM books
GROUP BY author_id;

-- SQL Server
SELECT author_id, STRING_AGG(title, ', ') WITHIN GROUP (ORDER BY title) AS titles
FROM books
GROUP BY author_id;

-- Oracle
SELECT author_id, LISTAGG(title, ', ') WITHIN GROUP (ORDER BY title) AS titles
FROM books
GROUP BY author_id;

Usage notes

  • Order is not guaranteed unless the database lets you state it inside the function. In SQLite the portable habit is to sort in a subquery first, as above.
  • Separators. SQLite's default is , with no space; pass ', ' for readable text.
  • Result size. The joined text can get large. MySQL truncates silently, Oracle raises an error. Aggregate only what you need.
  • Not a data model. A comma-separated column is hard to query and index. Use string aggregation for reports, not for storage.
  • Splitting back is awkward; it needs a recursive query or a vendor function.
  • JSON alternative. For structured output use JSON aggregates.