1. Home
  2. Blog
  3. Databases & SQL

Databases & SQL

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.

8 min read
SQL STRING_AGG: combine rows into one string

Key takeaways

  • 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_AGG in PostgreSQL, SQL Server and SQLite, GROUP_CONCAT in MySQL and SQLite, LISTAGG in 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_CONCAT output at group_concat_max_len, which defaults to 1,024 bytes, with only a warning. Raise it per session with SET SESSION group_concat_max_len = 100000;.
  • Oracle raises an error when LISTAGG exceeds the maximum string size. From 12.2 you can add ON OVERFLOW TRUNCATE.
  • SQL Server returns nvarchar(4000) or varchar(8000) for short input types. Cast the input to nvarchar(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

  1. Relying on default order. Always add ORDER BY inside the aggregate if order matters.
  2. Forgetting NULLs are skipped. Use COALESCE when every row must show.
  3. Expecting an empty string for empty groups. You get NULL.
  4. Using the wrong function name for your database. Check the table above.
  5. Ignoring length limits, especially MySQL’s silent 1,024 byte cut.
  6. 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

Work with Stack Quora

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.

Keep reading

More guides on testing, AI & modern development

Enjoyed this article? Pick a topic and jump straight into our newest tutorials.

Artificial Intelligence

Jaccard Similarity: Formula, Python, MinHash

Jaccard similarity measures overlap between two sets as intersection over union. Work the formula, compute it in Python for words…

Read article →
Software Development

Unix Timestamp Explained: Seconds vs ms

A Unix timestamp counts seconds since 1 January 1970 UTC. Learn seconds vs milliseconds, converting in Python, JavaScript and SQL,…

Read article →
DevOps

Git Hooks: Pre-commit and Commit-msg Examples

Git hooks run scripts at key moments such as commit and push. See tested pre-commit, commit-msg and prepare-commit-msg hooks, sharing…

Read article →
Test Automation

API Testing in Python: Pytest + Requests Suite

Build an API testing suite in Python with pytest and requests: fixtures, status codes, headers, JSON schema validation, CRUD flows,…

Read article →
Software Testing

Test Design Techniques: 9 Methods + Examples

Test design techniques help you write fewer tests that catch more bugs. Learn 9 black-box, white-box and experience-based methods with…

Read article →
Databases & SQL

SQL Temporary Table: Create, Use and Drop It

SQL temporary tables store interim results for one session. Learn syntax for SQL Server, PostgreSQL, MySQL, Oracle and SQLite, session…

Read article →
Databases & SQL

SQL CROSS JOIN: Every Combination + Examples

SQL CROSS JOIN returns every combination of rows from two tables. See the row math, product variants, calendar grids that…

Read article →
Software Development

JavaScript Date: Format, Parse, Timezone Bugs

JavaScript Date explained with tested Node.js code: zero-based months, the date-only UTC parsing trap, toISOString, getTimezoneOffset, Intl formatting, date math…

Read article →
Software Development

Python Requests: GET, POST, Timeouts, Retries

Python requests explained with code tested against a local API: GET with params, POST json vs data, headers and auth,…

Read article →
Software Development

Python Deepcopy vs Copy: Fix Nested Lists

Python deepcopy vs shallow copy explained with tested code: aliasing, nested lists, the [[0]*3]*3 trap, memo and cycles, custom __deepcopy__,…

Read article →
Artificial Intelligence

TF-IDF Explained: Formula and Worked Example

TF-IDF scores a word by how often it appears in a document and how rare it is across all documents….

Read article →
Software Development

Base64 Encoding Explained, With Python Code

Base64 encoding turns bytes into 64 safe text characters. See the bit-level steps, padding, URL-safe base64, the 33% size cost,…

Read article →
DevOps

Git Tag: Create, Push and Delete Release Tags

Git tag explained with tested commands: annotated vs lightweight tags, listing and sorting, pushing with follow-tags, deleting local and remote…

Read article →
Test Automation

Pytest Coverage: pytest-cov and Branch Gaps

Measure pytest coverage with pytest-cov: term-missing reports, branch coverage, HTML reports, pyproject settings and a fail-under gate for CI, with…

Read article →
Software Testing

Error Guessing in Testing: Checklist + Demo

Error guessing uses tester experience to predict where code breaks. Get a ready checklist of risky inputs, a step by…

Read article →
Databases & SQL

SQL LAG and LEAD: Compare Rows, 5 Examples

SQL LAG and LEAD read values from the previous or next row without a self join. Five tested examples: change,…

Read article →
Databases & SQL

SQL STRING_AGG: Combine Rows Into One String

SQL STRING_AGG joins values from many rows into one string per group. See syntax for PostgreSQL, SQL Server, MySQL GROUP_CONCAT,…

Read article →
Software Development

JavaScript Classes: Private Fields and Extends

JavaScript classes explained with tested Node.js code: constructors, methods, #private fields, getters, static members, extends and super, plus the errors…

Read article →
Artificial Intelligence

BM25 Explained: How Keyword Search Ranks Results, With Python Code and Hybrid Search

BM25 explained with a pure Python implementation we ran: the formula, IDF, term saturation, the k1 and b parameters, why…

Read article →
DevOps

Git Bisect Explained: Find the Commit That Broke Your Code, Manually or Automatically

Git bisect explained with a tested repository: find the one bad commit among 41 in 5 test runs, automate it…

Read article →
Test Automation

pytest Markers Explained: Custom Marks, skip, skipif, xfail and -m Filters

pytest markers explained with a tested suite: register custom marks, run only smoke tests with -m, combine filters with and,…

Read article →
Software Testing

Positive vs Negative Testing: Differences, 24 Real Test Cases and a Checklist

Positive vs negative testing explained with a real signup form: 6 positive and 18 negative test cases run in pytest,…

Read article →
Databases & SQL

SQL ORDER BY Multiple Columns: ASC, DESC, NULLS LAST, Case and Custom Order

SQL ORDER BY with multiple columns explained with tested queries: mixed ASC and DESC, tie-breakers, NULLS FIRST and LAST, case-insensitive…

Read article →
Databases & SQL

SQL Running Total: SUM OVER, Per-Group Totals, Moving Sums and the RANGE Trap

How to calculate a SQL running total with SUM() OVER: per-group totals, daily cumulative sums, moving sums, percent of total,…

Read article →
Artificial Intelligence

Cosine Similarity Explained: Formula, Worked Example and Python Code

Cosine similarity explained simply: the formula, a worked example by hand, pure Python and NumPy code, cosine vs Euclidean distance…

Read article →
DevOps

Git Squash Commits: 4 Easy Ways, Tested Step by Step (rebase -i, reset, merge)

Git squash commits explained with 4 tested methods: interactive rebase, reset –soft, merge –squash and fixup with autosquash. Before-and-after logs,…

Read article →
Test Automation

pytest parametrize Explained: 7 Patterns with Tested Examples

pytest parametrize explained with 7 tested patterns: basic cases, readable ids, stacked decorators, pytest.param with xfail and skip, indirect fixtures…

Read article →
Software Testing

Retesting vs Regression Testing: 8 Key Differences with a Real Bug Fix Example

Retesting vs regression testing explained with a real bug fix run in pytest: retesting confirmed the fix, the regression run…

Read article →
Artificial Intelligence

RAG Chunking Strategies Explained: Fixed, Overlap, Sentence, Recursive and Headings, Measured

RAG chunking strategies compared on one document with tiktoken 0.14: fixed-size, overlap, sentence packing, recursive splitting and heading-based chunks, measuring…

Read article →
DevOps

Git Stash Explained: Save, Apply, Pop and Untracked Files, Tested

Git stash explained with commands we ran on Git 2.56: push with a message, untracked files, list and show, apply…

Read article →
Software Testing

Pairwise Testing Explained: How All-Pairs Cuts 216 Tests to 13, Tested

Pairwise testing explained with a generator we ran in Python 3.13: a checkout with 216 combinations covered by 13 tests,…

Read article →
Test Automation

Test Doubles Explained: Dummy, Stub, Spy, Mock and Fake, with Tested Python Code

Test doubles explained with one Python example run in pytest 9.2: dummy, stub, spy, mock and fake, when to use…

Read article →
Artificial Intelligence

LLM Context Window Explained: Tokens, Limits and How to Fit More, Measured

The LLM context window explained with token counts we measured using tiktoken: words vs tokens for prose, code and Hindi,…

Read article →
DevOps

Git Cherry-Pick Explained: Copy the Commit You Need, Safely, Tested

Git cherry-pick explained with commands we ran in Git 2.56: copying a hotfix to a release branch, new hashes and…

Read article →
Test Automation

Snapshot Testing Explained: How It Works, When It Helps and When It Hurts

Snapshot testing explained with a pytest and syrupy demo we ran: creating snapshots, reading the diff after a code change,…

Read article →
Software Testing

Mutation Testing Explained: Why 100% Coverage Still Missed Our Bug

Mutation testing explained with a small mutation tool we built and ran: 14 mutants, a test suite with 100% line…

Read article →