SQL
Group and Concatenate Related String Values
Aggregate multiple related string values into a single comma-separated string per group using GROUP_CONCAT (MySQL) or STRING_AGG (PostgreSQL/SQL Server).
-- For MySQL:
SELECT
user_id,
GROUP_CONCAT(DISTINCT permission_name ORDER BY permission_name SEPARATOR ', ') AS user_permissions
FROM
user_permissions_map upm
JOIN
permissions p ON upm.permission_id = p.id
GROUP BY
user_id;
-- For PostgreSQL / SQL Server:
-- SELECT
-- user_id,
-- STRING_AGG(DISTINCT permission_name, ', ' ORDER BY permission_name) AS user_permissions
-- FROM
-- user_permissions_map upm
-- JOIN
-- permissions p ON upm.permission_id = p.id
-- GROUP BY
-- user_id;
How it works: This snippet demonstrates how to aggregate multiple string values from related rows into a single string within each group. This is incredibly useful for displaying a list of tags, categories, or permissions associated with an entity in a concise format. The example shows `GROUP_CONCAT` for MySQL and provides the equivalent `STRING_AGG` syntax for PostgreSQL and SQL Server, making the technique applicable across different database systems.