What string aggregation actually does

This is an aggregate function like SUM() or COUNT() (see SQL Aggregate Functions Explained Simply), except instead of producing a number, it produces a single delimited string built from every value in the group — the standard way to turn "many rows" into "one readable line" for a report, an export, or a UI label.

Rows inString out
tag: "sql", "sql", "database""sql, database" (per group, deduplicated if needed)

PostgreSQL & SQL Server: STRING_AGG

string_agg.sql
SELECT customer_id,
  STRING_AGG(product_name, ', ') AS products_ordered
FROM order_items
GROUP BY customer_id;
-- 101 | Keyboard, Mouse, Monitor

The separator is required as the second argument in both PostgreSQL and SQL Server — there's no implicit default, unlike MySQL's GROUP_CONCAT below.

MySQL: GROUP_CONCAT

group_concat.sql
SELECT customer_id,
  GROUP_CONCAT(product_name SEPARATOR ', ') AS products_ordered
FROM order_items
GROUP BY customer_id;
-- SEPARATOR is optional — defaults to a comma with no space

Unlike the other two, MySQL lets you omit the separator entirely, defaulting to a plain comma. It also supports DISTINCT directly inside the function: GROUP_CONCAT(DISTINCT product_name).

Oracle: LISTAGG

listagg.sql
SELECT customer_id,
  LISTAGG(product_name, ', ') WITHIN GROUP (ORDER BY product_name) AS products_ordered
FROM order_items
GROUP BY customer_id;

Oracle is the strictest of the three: WITHIN GROUP (ORDER BY ...) is mandatory syntax, not optional — you cannot call LISTAGG without specifying an order, even if you don't care what it is.

Controlling the order of concatenated values

All three let you control the order values appear in inside the string, but the syntax placement differs:

DatabaseOrdering syntax
PostgreSQL / SQL ServerSTRING_AGG(col, ', ' ORDER BY col) — inside the same call
MySQLGROUP_CONCAT(col ORDER BY col SEPARATOR ', ') — inside the same call
OracleLISTAGG(col, ', ') WITHIN GROUP (ORDER BY col) — separate, required clause

Without an explicit order, the sequence of values inside the string is not guaranteed to be stable across runs in any of the three — if a specific order matters for the output (alphabetical, chronological), always specify it.

How each one handles NULL

All three follow the same rule as every other aggregate function: NULL values in the aggregated column are silently skipped, not represented as an empty entry or the literal string "NULL" in the output. See NULL Handling in SQL and SQL Aggregate Functions Explained Simply for the underlying pattern this follows.

Combining with GROUP BY

String aggregation is almost always paired with GROUP BY, turning a one-to-many relationship into a single readable row per parent — a common alternative to returning multiple joined rows when a flat report or export is the goal:

customer_idproducts_ordered
101Keyboard, Mouse, Monitor
102Webcam

Every rule from SQL GROUP BY Interview Questions still applies here — every non-aggregated SELECT column must be in GROUP BY, exactly as it would with SUM() or COUNT().

The gotcha in each database

DatabaseGotcha
MySQLGROUP_CONCAT silently truncates at group_concat_max_len (a few hundred to a few thousand characters by default) — a long result can be quietly cut off with no error.
OracleLISTAGG raises a runtime error if the combined string exceeds the maximum VARCHAR2 length, unless an ON OVERFLOW TRUNCATE clause is added to handle it gracefully.
PostgreSQL / SQL ServerSTRING_AGG has no built-in length limit issue in practice, but omitting ORDER BY means the concatenation order is not guaranteed to be stable across query re-runs.

Key takeaways

  • STRING_AGG (Postgres/SQL Server), GROUP_CONCAT (MySQL), and LISTAGG (Oracle) all do the same job.
  • Oracle's WITHIN GROUP (ORDER BY ...) is mandatory; the other two make explicit ordering optional but recommended.
  • All three silently skip NULL values, consistent with every other SQL aggregate function.
  • MySQL's GROUP_CONCAT has a silent truncation limit that catches people off guard on large result sets.
  • Always specify an explicit ORDER BY if the order of concatenated values actually matters.