SQL
Aggregate Multiple Related Strings into One Column
Discover how to combine multiple string values from related rows into a single, comma-separated string within one column using SQL's aggregation functions.
-- PostgreSQL / SQL Server
SELECT
main_table.id,
main_table.name,
STRING_AGG(related_table.item_name, ', ') AS aggregated_items
FROM
main_table
JOIN
related_table ON main_table.id = related_table.main_id
GROUP BY
main_table.id, main_table.name;
-- MySQL / SQLite equivalent
-- SELECT
-- main_table.id,
-- main_table.name,
-- GROUP_CONCAT(related_table.item_name SEPARATOR ', ') AS aggregated_items
-- FROM
-- main_table
-- JOIN
-- related_table ON main_table.id = related_table.main_id
-- GROUP BY
-- main_table.id, main_table.name;
How it works: This query aggregates multiple related string values into a single column. It joins a `main_table` with a `related_table` and then uses `STRING_AGG` (or `GROUP_CONCAT` for MySQL/SQLite) to concatenate all `item_name` values for each `main_table.id`, separated by a comma and space. This is highly useful for summarizing related data, like listing all tags associated with a post or ingredients for a recipe, within a single result row.