BBampaa

Lessons › SQL › Deep Dive: Advanced Window Functions

SQL lesson

STRING_AGG()

STRING_AGG() collapses many rows of text into a single joined value, stitching them together with a separator you choose.

Try this lesson free →
About 5–10 minutes · no sign-up needed

Example

Combining Retail Details with STRING_AGG()

SELECT customer_id,
       STRING_AGG(category, ', ' ORDER BY category) AS purchased_categories
FROM orders
GROUP BY customer_id

Result:

customer_idpurchased_categories
C101Beauty, Clothing, Electronics
C102Electronics, Fashion

Step by step

  1. AS gives a column a friendlier name in the result.
    SELECT customer_id,
       STRING_AGG(category, ', ' ORDER BY category) AS purchased_categories
  2. FROM tells SQL which table to pull the data from.
    SELECT customer_id,
       STRING_AGG(category, ', ' ORDER BY category) AS purchased_categories
FROM orders
  3. GROUP BY collapses rows that share the same value into one row per group.
    SELECT customer_id,
       STRING_AGG(category, ', ' ORDER BY category) AS purchased_categories
FROM orders
GROUP BY customer_id;

Practice

Try each one in your head first, then open the answer.

1. Using orders, write a query that concatenates all category values per order_id into a comma-separated list.
SELECT order_id, STRING_AGG(category, ', ') FROM orders GROUP BY order_id
2. Using store_sales, write a query that concatenates all store_id values per sale_id into a comma-separated list.
SELECT sale_id, STRING_AGG(store_id, ', ') FROM store_sales GROUP BY sale_id
3. Using products, write a query that concatenates all brand values per brand into a comma-separated list.
SELECT brand, STRING_AGG(brand, ', ') FROM products GROUP BY brand
4. Using reviews, write a query that concatenates all review_id values per product_id into a comma-separated list.
SELECT product_id, STRING_AGG(review_id, ', ') FROM reviews GROUP BY product_id
5. Using employees, write a query that concatenates all employee_id values per department into a comma-separated list.
SELECT department, STRING_AGG(employee_id, ', ') FROM employees GROUP BY department

Learn it by doing. This lesson is a short hands-on level on Bampaa: see it work, then build and write it yourself, with instant feedback.

Try this lesson free →
About 5–10 minutes · no sign-up needed