STRING_AGG in SQL: Concatenate Grouped Rows with a Delimiter and ORDER BY

Sometimes you don't want a row per value — you want a single cell: a comma-separated list of tags, the emails on an order, the names of everyone on a project. That's string aggregation: take all the values inside a group and glue them into one string with a delimiter. PostgreSQL gives you STRING_AGG, MySQL has GROUP_CONCAT, and ClickHouse uses the pair arrayStringConcat(groupArray(...)). Let's cover the basics, ordering, dedup, and the traps that bite people.

Basic STRING_AGG in PostgreSQL

Take a typical store schema: users, orders, and order item rows. The simplest case is rolling every user's email into one string:

SELECT STRING_AGG(email, ', ') AS all_emails
FROM users;

STRING_AGG(expression, delimiter) takes two arguments: what to concatenate and what to separate it with. Both must be text (or compatible types). If your value isn't a string, cast it explicitly with ::text or CAST:

SELECT STRING_AGG(id::text, ',') AS user_ids
FROM users;

Most often you pair it with GROUP BY. For example, gather the product names bought in each order:

SELECT
  o.id AS order_id,
  STRING_AGG(oi.product_name, ', ') AS products
FROM orders o
JOIN order_items oi ON oi.order_id = o.id
GROUP BY o.id;

The result is one row per order, with products holding "Mouse, Keyboard, Monitor". Like any aggregate, STRING_AGG skips NULLs: rows where product_name IS NULL are dropped, and no stray delimiter appears around them. Handy, but it can quietly hide gaps in your data.

ORDER BY inside the aggregate

Without an explicit order, the database concatenates values in an arbitrary order that can shift between runs. When order matters — and in reports it almost always does — add ORDER BY inside the aggregate's parentheses:

SELECT
  o.id AS order_id,
  STRING_AGG(oi.product_name, ', ' ORDER BY oi.product_name) AS products
FROM orders o
JOIN order_items oi ON oi.order_id = o.id
GROUP BY o.id;

You can sort by a different column too — not necessarily the one being concatenated. A common pattern is listing items in the order they were added:

SELECT
  o.id AS order_id,
  STRING_AGG(oi.product_name, ', ' ORDER BY oi.added_at DESC) AS products
FROM orders o
JOIN order_items oi ON oi.order_id = o.id
GROUP BY o.id;

To drop duplicates, put DISTINCT right before the expression. One catch: with DISTINCT, you can only ORDER BY the concatenated expression itself.

SELECT
  customer_country,
  STRING_AGG(DISTINCT currency, ', ' ORDER BY currency) AS currencies
FROM orders
GROUP BY customer_country;

MySQL: GROUP_CONCAT

MySQL's equivalent is GROUP_CONCAT, with its own syntax: the delimiter goes in a SEPARATOR clause, and ordering uses ORDER BY inside the function.

SELECT
  o.id AS order_id,
  GROUP_CONCAT(oi.product_name ORDER BY oi.product_name SEPARATOR ', ') AS products
FROM orders o
JOIN order_items oi ON oi.order_id = o.id
GROUP BY o.id;

DISTINCT works as well: GROUP_CONCAT(DISTINCT currency SEPARATOR ', '). The default separator is a comma, so you can omit SEPARATOR if that suits you.

• The big MySQL trap: the result is truncated at group_concat_max_len (1024 bytes by default), and it happens silently — no error. On long lists you'll get a cut-off string. Raise it per session: SET SESSION group_concat_max_len = 1000000;.

ClickHouse: arrayStringConcat(groupArray())

ClickHouse has no direct STRING_AGG; you compose two functions instead. First groupArray() collects a group's values into an array, then arrayStringConcat() joins that array into a delimited string:

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

For ordering, wrap the collected array in arraySort; for uniqueness, use groupUniqArray instead of groupArray:

SELECT
  customer_country,
  arrayStringConcat(arraySort(groupUniqArray(currency)), ', ') AS currencies
FROM orders
GROUP BY customer_country;

Note that arrayStringConcat only handles strings: convert numeric fields with toString(...) before collecting them into the array, or you'll hit a type error.

Granularity and pitfalls

The most common shared trap is JOIN-induced duplication. If you also join a payments table to orders, each order row multiplies by its number of payments, and STRING_AGG without DISTINCT repeats products several times. Fix it with DISTINCT, or by aggregating in a subquery before the join:

SELECT
  o.id,
  pr.products,
  SUM(p.amount) AS paid
FROM orders o
JOIN payments p ON p.order_id = o.id
JOIN (
  SELECT order_id, STRING_AGG(product_name, ', ' ORDER BY product_name) AS products
  FROM order_items
  GROUP BY order_id
) pr ON pr.order_id = o.id
GROUP BY o.id, pr.products;

The essentials:

• STRING_AGG and friends skip NULL — wrap values in COALESCE if missing data is meaningful.
• Without ORDER BY inside the aggregate, order is not guaranteed.
• MySQL silently truncates at group_concat_max_len.
• Watch your JOIN granularity, or you'll get duplicates in the concatenated string.