SQL STRING_AGG Explained: Combine Many Rows Into One String
SQL STRING_AGG joins values from many rows into one delimited string per group, such as every product a customer bought. Learn the syntax in PostgreSQL, SQL Server, MySQL, Oracle and SQLite, plus ordering, DISTINCT and NULL rules, with tested output.
- SQL STRING_AGG is an aggregate function: it turns many rows into one string per group, joined by a separator you choose.
- The name changes by database:
STRING_AGGin PostgreSQL, SQL Server and SQLite,GROUP_CONCATin MySQL and SQLite,LISTAGGin Oracle. - Without an explicit
ORDER BY, the order of items in the list is not guaranteed. - NULL values are skipped. A group with no values at all returns NULL, not an empty string.
Reports often need one row per customer with all their products in a single cell, or one row per article with its tags as a comma separated list. Without string aggregation, you would fetch every row and glue strings together in application code. SQL STRING_AGG does it inside the query, in one pass, next to your other aggregates such as COUNT and SUM.
We tested every query below on SQLite 3.50.4, which supports both string_agg and group_concat. Syntax for other databases is listed in a table later, with the differences that catch people out.
What does STRING_AGG do?
STRING_AGG(expression, separator) collects the expression from every row in a group and joins them with the separator. It works with GROUP BY exactly like SUM. Without GROUP BY, it collapses the whole result into a single string. If you need a refresher on grouping, read our GROUP BY and HAVING guide first.
Sample data
CREATE TABLE orders(id INTEGER PRIMARY KEY, customer TEXT, product TEXT, qty INT);
INSERT INTO orders VALUES
(1,'Asha','pen',2), (2,'Asha','book',1), (3,'Ravi','ink',5),
(4,'Asha','pen',1), (5,'Ravi',NULL,1), (6,'Meera','bag',1);
Note that Asha bought pens twice and Ravi has one order with no product recorded.
Basic syntax
SELECT customer, string_agg(product, ', ') AS products
FROM orders
GROUP BY customer;
| customer | products |
|---|---|
| Asha | pen, book, pen |
| Meera | bag |
| Ravi | ink |
One row per customer, products joined with a comma and a space. The separator is a plain string, so ' | ', '; ' or a newline all work.
Ordering the items in the list
The list above happens to follow insertion order, but the SQL standard does not promise that. Different plans, parallel workers or indexes can change it. Always state the order you want:
SELECT customer,
string_agg(product, ', ' ORDER BY product) AS products,
count(*) AS n
FROM orders
GROUP BY customer;
| customer | products | n |
|---|---|---|
| Asha | book, pen, pen | 3 |
| Meera | bag | 1 |
| Ravi | ink | 2 |
Now Asha’s list is alphabetical. Notice Ravi: n is 2, but only one product is listed. That leads to the next rule.
How NULLs behave
Like other aggregates, STRING_AGG skips NULL inputs. Ravi’s NULL product simply vanished. If a missing value should still show up, replace it first with COALESCE:
SELECT customer,
string_agg(coalesce(product, '(none)'), ' | ' ORDER BY id) AS p
FROM orders
GROUP BY customer;
| customer | p |
|---|---|
| Asha | pen | book | pen |
| Meera | bag |
| Ravi | ink | (none) |
When there are no input rows at all, the result is NULL, not an empty string. Our test SELECT string_agg(product, ',') FROM orders WHERE 1=0 returned NULL. Wrap the call in COALESCE(..., '') if your application expects text. Our COALESCE guide and NULL values guide cover this in more detail.
Removing duplicates with DISTINCT
Asha appears with “pen” twice. To list each product once, add DISTINCT. In SQLite, the simple form works:
SELECT customer, group_concat(DISTINCT product) AS uniq
FROM orders GROUP BY customer;
-- Asha | pen,book
But SQLite rejected a custom separator together with DISTINCT:
SELECT string_agg(DISTINCT customer, ',' ORDER BY customer) FROM orders;
-- OperationalError: DISTINCT aggregates must have exactly one argument
Support differs a lot. PostgreSQL allows STRING_AGG(DISTINCT x, ', ' ORDER BY x) as long as the ORDER BY uses the same expression. MySQL allows GROUP_CONCAT(DISTINCT x ORDER BY x SEPARATOR ', '). SQL Server’s STRING_AGG has no DISTINCT, so deduplicate in a subquery or CTE first, then aggregate. A CTE keeps that readable.
Building richer strings
The first argument can be any expression, so you can format each item before joining. Combined with HAVING, this gives compact order summaries:
SELECT customer,
string_agg(product || ' x' || qty, '; ' ORDER BY id) AS lines
FROM orders
WHERE product IS NOT NULL
GROUP BY customer
HAVING count(*) > 1;
-- Asha | pen x2; book x1; pen x1
Only Asha qualifies, as the only customer with more than one non-NULL order line. Use || for concatenation in PostgreSQL, SQLite and Oracle, CONCAT() in MySQL, and + or CONCAT() in SQL Server. See our SQL string functions guide for more.
Syntax by database
| Database | Function | Ordered form |
|---|---|---|
| PostgreSQL | STRING_AGG(x, ', ') |
STRING_AGG(x, ', ' ORDER BY x) |
| SQL Server 2017+ | STRING_AGG(x, ', ') |
... WITHIN GROUP (ORDER BY x) |
| MySQL / MariaDB | GROUP_CONCAT(x) |
GROUP_CONCAT(x ORDER BY x SEPARATOR ', ') |
| Oracle | LISTAGG(x, ', ') |
... WITHIN GROUP (ORDER BY x) |
| SQLite 3.44+ | string_agg(x, ', ') or group_concat(x) |
string_agg(x, ', ' ORDER BY x) |
Two details trip people up. MySQL puts the separator after the keyword SEPARATOR, not as a second argument, and its default separator is a comma with no space. SQL Server and Oracle put the ordering outside the parentheses in a WITHIN GROUP clause.
Length limits
Long lists can hit limits, and some of them fail quietly:
- MySQL truncates
GROUP_CONCAToutput atgroup_concat_max_len, which defaults to 1,024 bytes, with only a warning. Raise it per session withSET SESSION group_concat_max_len = 100000;. - Oracle raises an error when
LISTAGGexceeds the maximum string size. From 12.2 you can addON OVERFLOW TRUNCATE. - SQL Server returns
nvarchar(4000)orvarchar(8000)for short input types. Cast the input tonvarchar(max)if results might be longer.
Testing tip: add a test with a group large enough to cross 1,024 characters. Silent truncation in MySQL is a classic production bug that small test data never reveals.
Real use cases
- Tag lists per article or product for search pages.
- Order summaries in emails and invoices.
- Audit reports showing every role a user holds in one cell.
- Test data checks, for example comparing the list of statuses per order against an expected string.
- Building IN lists for a quick ad hoc query. Never paste these into application SQL, because that invites injection.
Going the other direction, from one string back into rows, needs a split function such as STRING_SPLIT in SQL Server or string_to_table in PostgreSQL. And if you want values spread across columns rather than joined in one cell, that is a pivot, covered in our SQL pivot guide.
Common mistakes
- Relying on default order. Always add ORDER BY inside the aggregate if order matters.
- Forgetting NULLs are skipped. Use COALESCE when every row must show.
- Expecting an empty string for empty groups. You get NULL.
- Using the wrong function name for your database. Check the table above.
- Ignoring length limits, especially MySQL’s silent 1,024 byte cut.
- Storing aggregated strings in tables. Keep data in rows and aggregate at query time, which keeps your schema normalised.
When to aggregate into JSON instead
A delimited string is great for humans and reports. It is fragile for machines. If a value itself contains the separator, you can no longer split the result reliably. We added a product named “pen, red” and aggregated it two ways:
SELECT string_agg(product, ', ' ORDER BY id) FROM orders WHERE customer = 'Zoe';
-- pen, red, ink (two products or three?)
SELECT json_group_array(product) FROM orders WHERE customer = 'Zoe';
-- ["pen, red","ink"] (clearly two)
A JSON array keeps every value intact, and your application can parse it with any JSON library. You can even build objects per row:
SELECT customer, json_group_array(json_object('p', product, 'q', qty))
FROM orders WHERE customer = 'Asha' GROUP BY customer;
-- [{"p":"pen","q":2},{"p":"book","q":1},{"p":"pen","q":1}]
Watch the NULL handling, which differs from STRING_AGG. In our run, Ravi’s JSON array came back as ["ink",null]: SQLite’s json_group_array keeps NULLs instead of skipping them. The equivalents are json_agg or jsonb_agg in PostgreSQL, JSON_ARRAYAGG in MySQL and Oracle, and FOR JSON in SQL Server. Our SQL JSON functions guide covers them. As a rule, use STRING_AGG for display and JSON aggregation for data that code will read back.
Final thoughts
SQL STRING_AGG and its cousins GROUP_CONCAT and LISTAGG turn many rows into one readable string per group. Pick the right name for your database, always specify the order, decide how NULLs should appear, and watch the length limits. With those four habits, string aggregation becomes one of the handiest tools in your reporting queries.
FAQ
What does STRING_AGG do in SQL?
It is an aggregate function that joins values from many rows into one string, separated by a delimiter you choose, usually one string per GROUP BY group.
What is the MySQL equivalent of STRING_AGG?
MySQL uses GROUP_CONCAT. Put ORDER BY and the SEPARATOR keyword inside the call, and raise group_concat_max_len for long results.
Does STRING_AGG ignore NULL values?
Yes. NULL inputs are skipped. Wrap the column in COALESCE if you want a placeholder to appear instead.
How do I sort values inside STRING_AGG?
In PostgreSQL and SQLite add ORDER BY inside the call. In SQL Server and Oracle add WITHIN GROUP with ORDER BY after the call.
Which SQL Server version supports STRING_AGG?
STRING_AGG is available from SQL Server 2017. Older versions use the FOR XML PATH workaround.
Keep learning
Need help with testing, WordPress or SEO?
We help teams ship better software and grow their sites. Tell us what you are working on and we will suggest a clear next step.