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. That makes it the tool 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_id
The 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. SQLite has accepted
string_agg
too since version 3.44.
Schema · Garden Shop Tables · products, categories 2 tables
One row per product, with price, cost, and inventory levels.
Lookup table of product categories.
For each category, return category_name, the count of
products, and a product_list: every product name
in that category, in alphabetical order, joined by ", ".
Order the rows by category name.
Run your query to inspect the result.
Expected answer
- 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
COUNT, SUM, AVG, MIN, MAX, GROUP BY, and HAVING.
COUNT, SUM, AVG, GROUP BY, and HAVING.
Practice aggregate reports, HAVING, and answer shape.
Avoid truncating fractional results.
Filter groups with HAVING, rows with WHERE.
Searched and simple CASE patterns.