Listing Group Values with STRING_AGG in SQL
Sometimes a report needs the members of each group, not just a count. STRING_AGG rolls them into one readable cell.
Most aggregates reduce a group to a number. string_agg is different: it concatenates a column's values across the group into a single string, joined by a separator you choose. It's perfect for a "who / what's in this group" column.
Add order by inside the aggregate so the list is stable and readable. Without it, the order of the concatenated values isn't guaranteed.
select order_id,
string_agg(product_name, ', ' order by product_name) as items
from order_lines
group by order_idThe function name varies by database: it's string_agg in DuckDB, Postgres, and SQL Server, group_concat in MySQL and SQLite, and listagg in Oracle, but the idea is the same.
Schema · Garden ShopTable · products9 columns · 24 rows
One row per product, with price, cost, and inventory levels.
For each category, return category_name, the count ofproducts, and a product_list: every product name in that category, in alphabetical order, joined by ", ". Order the rows by category name.
- Columns: category_name, products, product_list.
- Rows: one per category (8 rows).
- product_list holds every product name in the category, comma-separated and alphabetical.
Related
Split a group into side-by-side columns with FILTER.
Add subtotal and total rows to a grouped report.
Group on a CASE expression to bucket values.
Return several subtotal levels in one grouped query.