âš¡IntermediateAdvanced Functions
STRING_AGG and ARRAY_AGG
Aggregate multiple rows into a comma-separated string or an array.
Overview
STRING_AGG(expr, delimiter) concatenates multiple values into a single string. ARRAY_AGG(expr) aggregates values into a PostgreSQL array. Both support ORDER BY inside the aggregate to control the order of values, and FILTER to conditionally include values.
These are essential for "pivot-like" queries returning related rows collapsed into a single column rather than multiple rows.
Key Points
- •STRING_AGG(col, ', ') comma-separated list of values
- •STRING_AGG(col, ', ' ORDER BY col) sorted list
- •ARRAY_AGG(col) aggregate to array
- •ARRAY_AGG(DISTINCT col ORDER BY col) distinct sorted values
- •Both skip NULLs by default
- •Use with GROUP BY to get one row per group with aggregated values
Examples
STRING_AGG and ARRAY_AGG
-- Comma-separated tags per post
SELECT post_id, STRING_AGG(tag, ', ' ORDER BY tag) AS tags
FROM post_tags
GROUP BY post_id;
-- Array of order IDs per customer
SELECT customer_id, ARRAY_AGG(id ORDER BY created_at) AS order_ids
FROM orders GROUP BY customer_id;
-- Distinct values
SELECT STRING_AGG(DISTINCT category, ', ' ORDER BY category) AS categories FROM products;Exercises
Write SQL to solve each exercise. Your progress is saved automatically.
Loading editor…
Completed: 0 / 5 exercises
Knowledge Check
Question 1 of 4