sql string_agg group_concat equivalent

TL;DR

Postgres: string_agg(col, ', '). MySQL: group_concat(col SEPARATOR ', '). SQL Server: string_agg(col, ', '). BigQuery: string_agg(col, ', '). SQLite: group_concat(col, ', '). Same idea everywhere, different spelling, and only some support ordering or distinct inside.

sql string_agg group_concat equivalent

Use this when

• You need concatenated strings per group (tags, emails, ids)
• Porting queries between database dialects
• You need ordering or dedup inside the concatenation

Not for this skill when

• You need to pivot rows into columns
• You need window-function ranking
• You need JSON output instead of delimited strings

Steps

1. The Postgres form, the reference implementation:

SELECT user_id, string_agg(tag, ', ' ORDER BY tag) AS tags
FROM user_tags
GROUP BY user_id;

Expected output: one row per user with tags joined and sorted. The ORDER BY inside the aggregate is the part people miss; without it the order is arbitrary.

1. MySQL's spelling:

SELECT user_id, GROUP_CONCAT(tag ORDER BY tag SEPARATOR ', ') AS tags
FROM user_tags
GROUP BY user_id;

Expected output: the same result. Note MySQL puts the separator in a SEPARATOR clause, not as a second argument.

1. SQL Server and BigQuery:

-- SQL Server 2017+:
SELECT user_id, STRING_AGG(tag, ', ') WITHIN GROUP (ORDER BY tag) AS tags
FROM user_tags GROUP BY user_id;
-- BigQuery:
SELECT user_id, STRING_AGG(tag, ', ' ORDER BY tag) AS tags
FROM user_tags GROUP BY user_id;

Expected output: concatenated strings. SQL Server needs the WITHIN GROUP wrapper for ordering; BigQuery follows the Postgres style.

1. Deduplicate inside the aggregation when the source has repeats:

-- Postgres:
SELECT user_id, string_agg(DISTINCT tag, ', ' ORDER BY tag) AS tags
FROM user_tags GROUP BY user_id;

Expected output: each tag appears once. MySQL's GROUPCONCAT also takes DISTINCT; SQL Server's STRINGAGG does not support DISTINCT (dedupe in a subquery first).

1. Watch the length limits on big groups:

MySQL: group_concat_max_len (default 1024 chars) silently truncates.
Set it higher per session when needed.
Others: limited by the text type max, effectively unbounded.

Expected output: awareness. MySQL's truncation is silent, which is how "missing tags" bugs are born. Check SHOW VARIABLES LIKE 'group_concat_max_len' when results look cut off.

Variant phrasings

mysql group_concat postgres equivalent

string_agg(col, delimiter) (step 1). The argument order is the same, only the name and separator syntax differ.

sql server string_agg distinct workaround

STRINGAGG has no DISTINCT. Dedupe first: `SELECT userid, STRINGAGG(tag, ', ') FROM (SELECT DISTINCT userid, tag FROM usertags) s GROUP BY userid`.

bigquery group_concat equivalent

STRING_AGG (step 3). BigQuery also has ARRAY_AGG if you want an array instead of a string.

Why it happens

Every database needs "collapse grouped strings into one," but the SQL standard never nailed the syntax, so each vendor invented its own. The semantics are nearly identical; the differences are ordering support, DISTINCT support, and separator placement.

Edge cases

• NULL values are skipped by all variants; if you need a placeholder for NULL, coalesce before aggregating.
• Ordering inside the aggregate is not guaranteed without the explicit ORDER BY clause.
• For very long concatenations consider whether the consumer really wants a string or would prefer an array/JSON.
• Older SQL Server (pre-2017) has no STRING_AGG; the workaround is the XML PATH hack, or upgrade.

Provenance

Resolved from the public thread: https://vectle.com/posts/pst_K60VId64JiOtZmUqmQgVeA