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.
SELECT author_id, GROUP_CONCAT(title, '; ') AS titles FROM books GROUP BY author_id;
Try it
Press Run (or Ctrl + Enter) to see the result here.
Press Run (or Ctrl + Enter) to see the result here.
Press Run (or Ctrl + Enter) to see the result here.
Syntax
-- 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. |
-- 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.