String Aggregation in SQL: GROUP_CONCAT vs STRING_AGG Compared

By Sharon Ben-Moshe · October 4, 2026 · 1 min read

Sharon Ben-Moshe is the founder of sqlfmt, a browser-based SQL formatter and validator with misspelled-keyword suggestions across PostgreSQL, MySQL, SQLite, SQL Server, and BigQuery.

Compare string aggregation across databases: MySQL GROUP_CONCAT, PostgreSQL string_agg, SQL Server STRING_AGG, and SQLite group_concat.

Quick answer

String aggregation collapses multiple row values into a single delimited string per group. In PostgreSQL and SQL Server 2017+, use STRING_AGG(column, delimiter). In MySQL and SQLite, use GROUP_CONCAT(column [SEPARATOR delimiter]). PostgreSQL and MySQL allow custom sorting directly inside the aggregate function call.

FAQs

How do I sort strings inside an aggregate list?

In PostgreSQL, use `string_agg(tag, ', ' ORDER BY tag ASC)`. In MySQL, use `GROUP_CONCAT(tag ORDER BY tag ASC SEPARATOR ', ')`. In SQL Server, use `STRING_AGG(tag, ', ') WITHIN GROUP (ORDER BY tag ASC)`.

Why did MySQL truncate my GROUP_CONCAT result?

MySQL limits GROUP_CONCAT output length to the value of `group_concat_max_len` (default 1024 bytes). Increase it using `SET SESSION group_concat_max_len = 1000000;`.

How do I eliminate duplicates from the aggregated string?

Include the DISTINCT keyword inside the function: `GROUP_CONCAT(DISTINCT role SEPARATOR ', ')` in MySQL or `string_agg(DISTINCT role, ', ')` in PostgreSQL.

Does SQLite support custom separators in group_concat?

Yes, SQLite's `group_concat(column, separator)` takes the separator as its second argument: `group_concat(tag, '; ')`.

SQL Dialects

The Comprehensive Guide to SQL Data Types: Storage, Precision, and Cross-Dialect Portability

By Sharon Ben-Moshe · Oct 13, 2026

The complete guide to SQL data types across PostgreSQL, MySQL, SQL Server, and SQLite. Numeric precision, character sets, timestamps, and JSON.

Read post

SQL Dialects

The Definitive Guide to SQL Window Functions: Syntax, Framing, and Real-World Recipes

By Sharon Ben-Moshe · Oct 13, 2026

The comprehensive guide to SQL window functions. Master OVER, PARTITION BY, ORDER BY, window frames (ROWS vs RANGE), and ranking recipes.

Read post

SQL Dialects

Try it now

Paste your SQL into sqlfmt — format, validate, and catch typos free in your browser.

Open SQL Formatter →

Side-by-side dialect syntax comparison

Notice how each database handles ordering and custom delimiters when aggregating user roles:

String aggregation syntax across 4 databases

-- PostgreSQL
SELECT
  u.username,
  string_agg(r.role_name, ', ' ORDER BY r.role_name ASC) AS roles
FROM users u
JOIN user_roles r ON u.id = r.user_id
GROUP BY u.id, u.username;

-- SQL Server (2017+)
SELECT
  u.username,
  STRING_AGG(r.role_name, ', ') WITHIN GROUP (ORDER BY r.role_name ASC) AS roles
FROM users u
JOIN user_roles r ON u.id = r.user_id
GROUP BY u.id, u.username;

-- MySQL
SELECT
  u.username,
  GROUP_CONCAT(DISTINCT r.role_name ORDER BY r.role_name ASC SEPARATOR ', ') AS roles
FROM users u
JOIN user_roles r ON u.id = r.user_id
GROUP BY u.id, u.username;

-- SQLite
SELECT
  u.username,
  group_concat(r.role_name, ', ') AS roles
FROM users u
JOIN user_roles r ON u.id = r.user_id
GROUP BY u.id, u.username;

The MySQL group_concat_max_len gotcha

A classic MySQL bug occurs when aggregating large lists: text silently cuts off at 1,024 characters. You must increase the session buffer to prevent truncated data:

Increasing MySQL buffer for large aggregates

-- Check current limit (often 1024)
SHOW VARIABLES LIKE 'group_concat_max_len';

-- Increase limit to 1MB for the current session
SET SESSION group_concat_max_len = 1048576;

Common mistakes to avoid

Forgetting that string_agg in PostgreSQL ignores NULL values, but an empty string `''` will produce unwanted consecutive delimiters.

Using XML PATH hacks in modern SQL Server: STRING_AGG replaced FOR XML PATH in SQL Server 2017.

Omitting ORDER BY inside string_agg, leading to unstable test snapshots where list items reorder randomly.

String Aggregation in SQL: GROUP_CONCAT vs STRING_AGG Compared

Quick answer

FAQs

How do I sort strings inside an aggregate list?

Why did MySQL truncate my GROUP_CONCAT result?

How do I eliminate duplicates from the aggregated string?

Does SQLite support custom separators in group_concat?

The Comprehensive Guide to SQL Data Types: Storage, Precision, and Cross-Dialect Portability

The Definitive Guide to SQL Window Functions: Syntax, Framing, and Real-World Recipes

The Ultimate Guide to SQL JOINs: Visualizing Every Join Type, Cardinality, and Performance

Key takeaways

Side-by-side dialect syntax comparison

The MySQL group_concat_max_len gotcha

Common mistakes to avoid

Official references

Quick answer

FAQs

How do I sort strings inside an aggregate list?

Why did MySQL truncate my GROUP_CONCAT result?

How do I eliminate duplicates from the aggregated string?

Does SQLite support custom separators in group_concat?

Keep reading

The Comprehensive Guide to SQL Data Types: Storage, Precision, and Cross-Dialect Portability

The Definitive Guide to SQL Window Functions: Syntax, Framing, and Real-World Recipes

The Ultimate Guide to SQL JOINs: Visualizing Every Join Type, Cardinality, and Performance

Key takeaways

Side-by-side dialect syntax comparison

The MySQL group_concat_max_len gotcha

Common mistakes to avoid

Related SQL guides

Official references