String Aggregation in SQL: GROUP_CONCAT, STRING_AGG and LISTAGG

1. Why string aggregation is a topic of its own

String aggregation solves a problem that classic aggregate functions like SUM or COUNT cannot cover: instead of a number, a composed text list should be produced per group, for example every tag of a product, every email address of a team, or every order line of an invoice in a single cell. Without string aggregation, this composition would either have to happen in the application layer after fetching multiple rows, or one gives up the compact representation entirely and shows every row individually.

The practical benefit of string aggregation shows up especially in reports, CSV exports and overview tables where a one to many relationship should be shown compactly in a single column. A report on orders that lists every contained product name in one column per order is an everyday example. Without string aggregation, the same order would appear once per product line as a separate row, which considerably worsens the readability of the report.

Every major relational database system offers string aggregation, though under a different function name and with slightly different syntax. MySQL uses GROUP_CONCAT, PostgreSQL and SQL Server use STRING_AGG, Oracle uses LISTAGG. This article walks through all four variants and shows what to watch for regarding ordering, separators and length limits in each.

2. GROUP_CONCAT in MySQL: syntax and options

GROUP_CONCAT is MySQL's own implementation of string aggregation and is one of the most frequently used functions in MySQL reports. The basic syntax combines an arbitrary expression column with optional additions for separator, sorting and duplicate filtering. Without further specification, GROUP_CONCAT defaults to a comma as the separator between individual values.

Particularly practical is the combination of DISTINCT, ORDER BY and SEPARATOR within the same function. DISTINCT removes duplicate values before the concatenation, ORDER BY determines the order of the values within the aggregated string, and SEPARATOR sets any custom separator, for example a semicolon or a line break. These three options together make GROUP_CONCAT one of the most flexible implementations of string aggregation overall.

-- String aggregation with GROUP_CONCAT in MySQL
SELECT
    p.product_id,
    p.product_name,
    GROUP_CONCAT(
        DISTINCT t.tag_name
        ORDER BY t.tag_name ASC
        SEPARATOR ', '
    ) AS tag_list
FROM products p
JOIN product_tags pt ON pt.product_id = p.product_id
JOIN tags t ON t.tag_id = pt.tag_id
GROUP BY p.product_id, p.product_name;

-- Result (excerpt)
-- product_id | product_name  | tag_list
--          1 | USB-C Cable   | Electronics, Cable, USB

3. STRING_AGG in PostgreSQL and SQL Server

PostgreSQL and SQL Server implement string aggregation under the same function name STRING_AGG, though with a structural difference compared to GROUP_CONCAT. With STRING_AGG, the separator is not an optional addition but a mandatory second parameter of the function itself, which rules out typos such as a forgotten separator from the start. The basic signature is STRING_AGG(expression, separator), where the expression typically needs to be cast to text before concatenation, unless it already is of type text.

For duplicate filtering, STRING_AGG in PostgreSQL places a DISTINCT directly before the expression, similar to other aggregate functions. Sorting happens through an optional ORDER BY inside the function, syntactically very similar to the GROUP_CONCAT approach in MySQL. SQL Server has supported STRING_AGG since version 2017, and WITHIN GROUP for sorting only since that same version alongside the base function, which needs to be taken into account with older SQL Server installations.

-- String aggregation with STRING_AGG in PostgreSQL
SELECT
    p.product_id,
    p.product_name,
    STRING_AGG(
        DISTINCT t.tag_name, ', '
        ORDER BY t.tag_name ASC
    ) AS tag_list
FROM products p
JOIN product_tags pt ON pt.product_id = p.product_id
JOIN tags t ON t.tag_id = pt.tag_id
GROUP BY p.product_id, p.product_name;

-- Equivalent in SQL Server (2017+), sorting via WITHIN GROUP
SELECT
    p.product_id,
    p.product_name,
    STRING_AGG(t.tag_name, ', ') WITHIN GROUP (ORDER BY t.tag_name ASC) AS tag_list
FROM products p
JOIN product_tags pt ON pt.product_id = p.product_id
JOIN tags t ON t.tag_id = pt.tag_id
GROUP BY p.product_id, p.product_name;

4. LISTAGG in Oracle and WITHIN GROUP

Oracle implements string aggregation under the name LISTAGG, which is conceptually closer to STRING_AGG than to GROUP_CONCAT. Sorting happens through the WITHIN GROUP clause, the same syntax that SQL Server adopted for STRING_AGG, which makes moving between the two systems easier. Without WITHIN GROUP, the order of values in LISTAGG is technically undefined, even though in practice the physical storage order is often observed.

A detail frequently overlooked with LISTAGG in Oracle: in older versions the function does not support a native DISTINCT for duplicate filtering, so duplicates must be removed before the call via a subquery with SELECT DISTINCT. Only newer Oracle versions offer a direct solution with LISTAGG(DISTINCT ...). This limitation is a good example of how string aggregation, despite a similar basic idea, does not translate one to one across systems.

-- String aggregation with LISTAGG in Oracle
SELECT
    p.product_id,
    p.product_name,
    LISTAGG(t.tag_name, ', ') WITHIN GROUP (ORDER BY t.tag_name ASC) AS tag_list
FROM products p
JOIN product_tags pt ON pt.product_id = p.product_id
JOIN tags t ON t.tag_id = pt.tag_id
GROUP BY p.product_id, p.product_name;

-- Older Oracle versions: deduplicate via subquery before LISTAGG
SELECT product_id, product_name,
       LISTAGG(tag_name, ', ') WITHIN GROUP (ORDER BY tag_name) AS tag_list
FROM (
    SELECT DISTINCT p.product_id, p.product_name, t.tag_name
    FROM products p
    JOIN product_tags pt ON pt.product_id = p.product_id
    JOIN tags t ON t.tag_id = pt.tag_id
)
GROUP BY product_id, product_name;

5. Enforcing deterministic ordering in string aggregation

A common mistake with string aggregation is assuming that the order of the concatenated values automatically corresponds to a sensible sort order. Without an explicit ORDER BY inside the aggregate function, the order is technically not guaranteed in many systems, even if it appears consistent in tests. This apparent consistency is exactly what makes it treacherous, because it can suddenly change after a database upgrade, a changed index structure or parallel processing, without the actual query changing at all.

The reliable solution is always an explicit ORDER BY inside the respective aggregate function, whether as its own clause part with GROUP_CONCAT and STRING_AGG in PostgreSQL, or as WITHIN GROUP with STRING_AGG in SQL Server and LISTAGG in Oracle. Anyone who needs reproducible lists sorted alphabetically or by creation date for a report should never leave this sorting to the chance of internal storage order, but always specify it explicitly in the string aggregation.

6. Length limits: group_concat_max_len and overflow errors

String aggregation produces a single string from multiple rows, whose length grows with the number of aggregated values. For large groups, such as all order lines of a long standing customer, this string can exceed internal length limits. MySQL limits the result of GROUP_CONCAT by default via the session variable group_concat_max_len, whose default value is often surprisingly low and silently truncates results instead of throwing an error.

PostgreSQL and SQL Server, by contrast, limit STRING_AGG implicitly through the maximum length of the underlying text data type, which rarely becomes a problem in practice but should still be kept in mind with extremely large groups. Oracle throws an explicit error with LISTAGG as soon as the combined string exceeds the internal limit of 4000 bytes, instead of silently truncating the result. Anyone working with very large groups should deliberately raise group_concat_max_len in MySQL and use the newer ON OVERFLOW clause of LISTAGG in Oracle to catch errors in a controlled way.

-- MySQL: raise the length limit for large string aggregation results
SET SESSION group_concat_max_len = 1000000;

SELECT
    customer_id,
    GROUP_CONCAT(order_id ORDER BY order_date SEPARATOR ', ') AS order_ids
FROM orders
GROUP BY customer_id;

-- Oracle: handle overflow explicitly instead of raising an error
SELECT
    customer_id,
    LISTAGG(order_id, ', ' ON OVERFLOW TRUNCATE '...' WITH COUNT)
        WITHIN GROUP (ORDER BY order_date) AS order_ids
FROM orders
GROUP BY customer_id;

7. String aggregation combined with GROUP BY in reporting

String aggregation shows its practical value almost always in combination with GROUP BY over several dimension columns. A typical report combines numeric metrics such as SUM or COUNT with a string aggregation of the involved detail values, so that report users see both the summary and the details in a single row. A report on orders per customer can thereby simultaneously show the total sum, the number of orders and a comma separated list of product categories, without a separate detail query.

What matters with this combination is that string aggregation and classic aggregate functions such as SUM operate in the same GROUP BY grouping, meaning they use the same grouping columns. If additional columns are accidentally included in GROUP BY, more, finer grained groups than intended arise, and the string aggregation returns fewer concatenated values per row than the report actually expects. This subtle interaction between grouping granularity and string aggregation is a frequent source of errors in more complex reports.

-- String aggregation combined with numeric aggregates in one report row
SELECT
    c.customer_id,
    c.customer_name,
    COUNT(DISTINCT o.order_id) AS order_count,
    SUM(o.total_amount) AS total_revenue,
    GROUP_CONCAT(DISTINCT cat.category_name ORDER BY cat.category_name SEPARATOR ', ') AS categories
FROM customers c
JOIN orders o ON o.customer_id = c.customer_id
JOIN order_items oi ON oi.order_id = o.order_id
JOIN categories cat ON cat.category_id = oi.category_id
GROUP BY c.customer_id, c.customer_name;

8. Performance: string aggregation in the database vs. the app

Running string aggregation directly in SQL is in nearly all cases more efficient than transferring raw data row by row to the application and concatenating it there. The reason lies in reduced network overhead: instead of transferring a thousand rows with tag names, the database transfers only a single, already concatenated row per group with string aggregation. For reports with many groups and large one to many relationships, this difference can reduce the transferred data volume by an order of magnitude.

A downside should still not be overlooked: string aggregation can cause noticeable CPU load on the database with very large groups and without suitable indexes on the involved join columns, because the concatenation itself costs computation time. For reports with millions of detail rows per group it is therefore worth checking the execution plan to make sure the underlying joins run over indexes before the actual string aggregation even begins.

9. String aggregation across database systems

The following overview summarizes the key differences between the four common implementations of string aggregation, so that switching between database systems does not hold any surprises regarding syntax or behavior.

Anyone who uses string aggregation from the start with explicit ORDER BY and deliberately set length limits avoids the typical surprises that only become visible as data volumes grow, such as silently truncated results or a suddenly different order after a database update.

10. Summary

String aggregation folds multiple rows of a group into a single text list and thereby replaces cumbersome concatenation in the application layer. MySQL uses GROUP_CONCAT with SEPARATOR for this, PostgreSQL and SQL Server use STRING_AGG with a mandatory separator parameter, Oracle uses LISTAGG with WITHIN GROUP for sorting. All four variants solve the same underlying problem, but differ in syntax, ordering guarantees and length limits.

Anyone using string aggregation in production should always set an explicit ORDER BY inside the function, deliberately configure length limits such as group_concat_max_len, and check system specific differences against the documentation for portable SQL. Applied correctly, string aggregation noticeably reduces both application layer complexity and the transferred data volume.

11. FAQ: String Aggregation in SQL