aggregate

String Aggregation With string_agg()

Concatenate related values into a single comma-separated list per group.

Overview

string_agg(expr, separator) is an aggregate that concatenates values from a group into one string — DuckDB's equivalent of MySQL's GROUP_CONCAT or Postgres' string_agg. It's perfect for "list all X per Y" reports, like every book title written by each author.

The Query

Library (Books/Authors/Publishers)

Loading database engine...

Step-by-Step Breakdown

string_agg(b.title, ', ') — joins each author's book titles with a comma-space separator.
ORDER BY b.published_year inside the aggregate controls the list order.
GROUP BY a.name — one row per author with their full bibliography.

Engine name differences

DuckDB and Postgres use string_agg; MySQL uses GROUP_CONCAT; SQL Server uses STRING_AGG. The concept — aggregate concatenation — is universal.

Variations

List product names per category in the store:

Store (Customers/Orders/Products)

Loading database engine...

Common Mistakes

NULLs disappear silently. string_agg skips NULL inputs; use COALESCE(col, '') if you need placeholders.
Forgetting GROUP BY. Without it, you get one giant concatenated string for the whole table.
Ordering confusion. The ORDER BY goes inside the aggregate call, not as a separate clause, to order the concatenated items.