List each customer's products on one line

List each customer's products on one line, like Earbuds, Speaker. New tool: STRING_AGG joins text from many rows into one, with a separator you choose. DISTINCT lists each product once, and ORDER BY inside puts them in A to Z order.

Try this lesson free →

About 5–10 minutes · no sign-up needed

Example

STRING_AGG: many rows into one text

SELECT customer_id,
       STRING_AGG(DISTINCT product, ', ' ORDER BY product) AS products
FROM orders
GROUP BY customer_id

Result:

customer_id	products
C101	Earbuds, Phone case, Speaker
C102	Charger, Earbuds

Step by step

One row per customer.

SELECT customer_id, STRING_AGG(DISTINCT product, ', ' ORDER BY product) AS products FROM orders GROUP BY customer_id

No join.

SELECT customer_id, STRING_AGG(DISTINCT product, ', ' ORDER BY product) AS products FROM orders GROUP BY customer_id

Every order counts.

SELECT customer_id, STRING_AGG(DISTINCT product, ', ' ORDER BY product) AS products FROM orders GROUP BY customer_id

STRING_AGG(DISTINCT product, ', ' ORDER BY product) with GROUP BY.

SELECT customer_id, STRING_AGG(DISTINCT product, ', ' ORDER BY product) AS products FROM orders GROUP BY customer_id

Practice

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

1. List each customer's products on one line, A to Z, each once, like Earbuds, Speaker. Show customer_id and products.

SELECT customer_id, STRING_AGG(DISTINCT product, ', ' ORDER BY product) AS products FROM orders GROUP BY customer_id

2. List the stores that sold each product on one line, A to Z. Show product and stores.

SELECT product, STRING_AGG(DISTINCT store, ', ' ORDER BY store) AS stores FROM orders GROUP BY product

3. List each store's coupons on one line, each once, A to Z. Show store and coupons.

SELECT store, STRING_AGG(DISTINCT coupon, ', ' ORDER BY coupon) AS coupons FROM orders GROUP BY store

4. List each customer's order ids on one line, oldest first, separated by ' / '. Show customer_id and order_ids.

SELECT customer_id, STRING_AGG(CAST(order_id AS TEXT), ' / ' ORDER BY order_date) AS order_ids FROM orders GROUP BY customer_id

5. List each store's products on one line with how many of each, like Earbuds (2). Show store and products.

WITH c AS (SELECT store, product, COUNT(*) AS n FROM orders GROUP BY store, product) SELECT store, STRING_AGG(CONCAT(product, ' (', n, ')'), ', ' ORDER BY product) AS products FROM c GROUP BY store

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