STRING_AGG()

Syntax

How it works

STRING_AGG() collapses many rows into one string. Where COUNT() and SUM() reduce a group to a number, STRING_AGG() reduces a group to text: it walks the rows in the group, takes the value of an expression from each, and joins them with a separator you choose. A classic use is listing every product that belongs to an order on a single line, or gathering all tags for an article into one comma separated field.

The important catch is that the function is not spelled the same everywhere. PostgreSQL and SQL Server 2017 and later both call it STRING_AGG(expression, separator), but they attach the sort order differently. PostgreSQL puts ORDER BY inside the call, as in STRING_AGG(name, ', ' ORDER BY name). SQL Server requires a separate WITHIN GROUP (ORDER BY ...) clause after the call. MySQL has no STRING_AGG() at all: the equivalent is GROUP_CONCAT(expression ORDER BY ... SEPARATOR separator), and it is the function you reach for on MySQL and MariaDB.

In every engine the function ignores NULL inputs, joins only non-NULL values, and is normally paired with GROUP BY so you get one concatenated string per group. See the aggregate functions guide for how it fits alongside the other reducers, and the full SQL functions list for related string tools.

Examples

List product names per order (PostgreSQL / SQL Server)

-- one row per order, all product names joined
SELECT order_id,
       STRING_AGG(product_name, ', ') AS products
FROM order_items
GROUP BY order_id;

order_id | products
---------+---------------------------
    1001 | Keyboard, Mouse, Monitor
    1002 | Cable, Adapter
    1003 | Laptop

The MySQL equivalent with GROUP_CONCAT

-- MySQL has no STRING_AGG; use GROUP_CONCAT.
-- Default separator is a comma with no space,
-- so set SEPARATOR to match ', '.
SELECT order_id,
       GROUP_CONCAT(product_name SEPARATOR ', ') AS products
FROM order_items
GROUP BY order_id;

order_id | products
---------+---------------------------
    1001 | Keyboard, Mouse, Monitor
    1002 | Cable, Adapter
    1003 | Laptop

Ordered aggregation (each engine)

-- PostgreSQL: ORDER BY goes inside the call
SELECT order_id,
       STRING_AGG(product_name, ', ' ORDER BY product_name) AS products
FROM order_items
GROUP BY order_id;

-- SQL Server 2017+: use WITHIN GROUP
SELECT order_id,
       STRING_AGG(product_name, ', ') WITHIN GROUP (ORDER BY product_name) AS products
FROM order_items
GROUP BY order_id;

-- MySQL: ORDER BY inside GROUP_CONCAT, before SEPARATOR
SELECT order_id,
       GROUP_CONCAT(product_name ORDER BY product_name SEPARATOR ', ') AS products
FROM order_items
GROUP BY order_id;

order_id | products
---------+---------------------------
    1001 | Keyboard, Monitor, Mouse

Distinct values only

-- PostgreSQL and SQL Server
SELECT customer_id,
       STRING_AGG(DISTINCT country, ', ') AS countries
FROM shipments
GROUP BY customer_id;

-- MySQL uses DISTINCT inside GROUP_CONCAT
SELECT customer_id,
       GROUP_CONCAT(DISTINCT country SEPARATOR ', ') AS countries
FROM shipments
GROUP BY customer_id;

customer_id | countries
------------+--------------
         42 | KSA, UAE
         57 | EGY

Aggregate across the whole table (no GROUP BY)

-- Every category name on one line
SELECT STRING_AGG(name, ' | ' ORDER BY name) AS all_categories
FROM categories;

all_categories
----------------------------
Books | Games | Music | Toys

Common mistakes

-- MySQL: long lists get silently cut off at
-- group_concat_max_len (often 1024 bytes)
SELECT order_id, GROUP_CONCAT(product_name)
FROM order_items
GROUP BY order_id;

-- Raise the limit for the session first
SET SESSION group_concat_max_len = 1000000;

SELECT order_id, GROUP_CONCAT(product_name SEPARATOR ', ')
FROM order_items
GROUP BY order_id;

-- Wrong: PostgreSQL syntax will not run on SQL Server
SELECT STRING_AGG(name, ', ' ORDER BY name)
FROM tags;

-- SQL Server: ordering lives in WITHIN GROUP
SELECT STRING_AGG(name, ', ') WITHIN GROUP (ORDER BY name)
FROM tags;

-- Expecting NULL rows to appear as blanks in the list
SELECT STRING_AGG(middle_name, ', ') AS names
FROM people;

-- NULLs are skipped; substitute a placeholder if
-- you need one entry per row
SELECT STRING_AGG(COALESCE(middle_name, '(none)'), ', ') AS names
FROM people;

Performance

STRING_AGG() and GROUP_CONCAT() build their result in memory, so cost grows with the size of each group. Concatenating a handful of values per group is cheap, but joining thousands of long strings per group forces the engine to hold large buffers and can spill to disk. If a report only needs a preview, cap the group with a subquery or a LIMIT per group rather than materialising everything.

An ORDER BY inside the aggregate adds a sort. When the ordering column matches an index that the optimiser can use for the grouping, that sort can be avoided; otherwise budget for it on large inputs. On MySQL remember that a result larger than group_concat_max_len is truncated rather than sorted in full, which can hide the true cost.

Because the output is a single wide value, watch the row size limits: SQL Server STRING_AGG caps at 8000 bytes unless the input is (N)VARCHAR(MAX), and very wide rows increase network transfer and client memory. For huge fan out prefer returning the detail rows and joining them in the application, or aggregate into JSON where the engine offers a streaming JSON aggregate.

Interview questions

SELECT order_id, STRING_AGG(product_name, ', ') AS products
FROM order_items
GROUP BY order_id;

SET SESSION group_concat_max_len = 1000000;
SELECT GROUP_CONCAT(name SEPARATOR ', ') FROM tags;

SELECT customer_id,
       GROUP_CONCAT(DISTINCT country ORDER BY country SEPARATOR ', ') AS countries
FROM shipments
GROUP BY customer_id;