Using STRING_AGG in SQL: A Practical Guide

-- Sample table: products
-- id | category | product_name
-- 1  | fruits   | apple
-- 2  | fruits   | banana
-- 3  | fruits   | orange
-- 4  | veggies  | carrot

-- Get all products in each category as a comma-separated list
SELECT 
    category,
    STRING_AGG(product_name, ', ') AS product_list
FROM products
GROUP BY category;

-- Output:
-- category | product_list
-- fruits   | apple, banana, orange
-- veggies  | carrot

-- Using different separators
SELECT 
    category,
    STRING_AGG(product_name, ' | ') AS product_list,
    STRING_AGG(product_name, ' → ') AS product_path
FROM products
GROUP BY category;

-- Output:
-- category | product_list         | product_path
-- fruits   | apple | banana | orange | apple → banana → orange
-- veggies  | carrot               | carrot

-- Sample table: sales
-- product_name | sales_amount
-- apple        | 1000
-- banana       | 2000
-- orange       | 1500

-- Concatenate products ordered by sales
SELECT STRING_AGG(product_name, ', ' ORDER BY sales_amount DESC) 
    AS products_by_sales
FROM sales;

-- Output:
-- products_by_sales
-- banana, orange, apple

-- Sample data with nulls
-- product | stock_status
-- apple   | in stock
-- banana  | NULL
-- orange  | out of stock

SELECT STRING_AGG(
    CASE 
        WHEN stock_status IS NOT NULL THEN product 
    END, 
    ', '
) AS available_products
FROM inventory
WHERE stock_status = 'in stock';

-- Output:
-- available_products
-- apple

-- Sample tables
-- posts
-- id | title
-- 1  | First Post
-- 2  | Second Post

-- post_tags
-- post_id | tag
-- 1       | sql
-- 1       | database
-- 2       | sql
-- 2       | tutorial

-- Get posts with their tags
SELECT 
    p.title,
    STRING_AGG(pt.tag, ', ' ORDER BY pt.tag) AS tags
FROM posts p
LEFT JOIN post_tags pt ON p.id = pt.post_id
GROUP BY p.id, p.title;

-- Output:
-- title       | tags
-- First Post  | database, sql
-- Second Post | sql, tutorial

-- Sample tables
-- orders
-- id | customer_id | order_date
-- 1  | 101        | 2024-01-01
-- 2  | 101        | 2024-01-15

-- order_items
-- order_id | product
-- 1        | laptop
-- 1        | mouse
-- 2        | keyboard

SELECT 
    o.customer_id,
    STRING_AGG(
        oi.product || ' (' || o.order_date || ')',
        '; '
        ORDER BY o.order_date
    ) AS order_history
FROM orders o
JOIN order_items oi ON o.id = oi.order_id
GROUP BY o.customer_id;

-- Output:
-- customer_id | order_history
-- 101        | laptop (2024-01-01); mouse (2024-01-01); keyboard (2024-01-15)

-- Sample table: user_activities
-- user_id | activity      | timestamp
-- 1       | login        | 2024-03-21 09:00
-- 1       | view_profile | 2024-03-21 09:05
-- 1       | logout       | 2024-03-21 09:30

SELECT 
    user_id,
    STRING_AGG(
        activity || ' at ' || 
        TO_CHAR(timestamp, 'HH24:MI'),
        ' → '
        ORDER BY timestamp
    ) AS activity_timeline
FROM user_activities
GROUP BY user_id;

-- Output:
-- user_id | activity_timeline
-- 1       | login at 09:00 → view_profile at 09:05 → logout at 09:30

-- Sample table: page_views
-- page_id | visitor_id
-- 1       | 101
-- 1       | 101  -- duplicate
-- 1       | 102

SELECT 
    page_id,
    STRING_AGG(DISTINCT CAST(visitor_id AS VARCHAR), ', ') AS unique_visitors
FROM page_views
GROUP BY page_id;

-- Output:
-- page_id | unique_visitors
-- 1       | 101, 102

-- Set maximum length for concatenated strings
SELECT 
    category,
    LEFT(STRING_AGG(description, ' '), 200) || '...' AS category_description
FROM products
GROUP BY category;

-- Using subquery for better performance with large datasets
SELECT 
    category,
    (
        SELECT STRING_AGG(product_name, ', ')
        FROM (
            SELECT product_name 
            FROM products p2 
            WHERE p2.category = p1.category
            LIMIT 5
        ) sub
    ) AS top_products
FROM products p1
GROUP BY category;

-- MySQL equivalent using GROUP_CONCAT
SELECT 
    category,
    GROUP_CONCAT(
        product_name
        ORDER BY product_name
        SEPARATOR ', '
    ) AS product_list
FROM products
GROUP BY category;

-- SQLite using GROUP_CONCAT
SELECT 
    category,
    GROUP_CONCAT(product_name, ', ') AS product_list
FROM products
GROUP BY category;

-- Wrong way - NULLs can break concatenation
STRING_AGG(column_name, ', ')

-- Right way - Handle NULLs explicitly
STRING_AGG(COALESCE(column_name, ''), ', ')

-- Wrong way - Undefined order
STRING_AGG(product_name, ', ')

-- Right way - Explicit ordering
STRING_AGG(product_name, ', ' ORDER BY product_name)

-- Potential memory issues with large datasets
STRING_AGG(long_text, ' ')

-- Better - Limit output length
LEFT(STRING_AGG(long_text, ' '), 1000)

-- Create JSON array of products
SELECT 
    category,
    '[' || STRING_AGG(
        '"' || product_name || '"',
        ','
        ORDER BY product_name
    ) || ']' AS products_json
FROM products
GROUP BY category;

-- Output:
-- category | products_json
-- fruits   | ["apple","banana","orange"]
-- veggies  | ["carrot"]