Using STRING_AGG in SQL: A Practical Guide
Blog
Breakdown of STRING_AGG in SQL
STRING_AGG turns multiple rows into a single concatenated string – a task that’s essential for reporting, data analysis, and API responses. Let’s explore how to use it effectively with real examples you can apply to your own databases.
Basic STRING_AGG Usage
Simple Concatenation
Start with a basic example:
-- 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 | carrotCustom Separators
You can use any separator, not just commas:
-- 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 | carrotAdvanced STRING_AGG Features
Ordering Results
Control the order of concatenated items:
-- 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, appleFiltering NULL Values
Handle NULL values appropriately:
-- 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
-- appleReal-World Applications
Building Tag Systems
Create a tag management system:
-- 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, tutorialCustomer Order History
Summarize order history:
-- 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)Activity Logs
Create readable activity summaries:
-- 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:30Performance Optimization
Using DISTINCT
Remove duplicates while aggregating:
-- 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, 102Handling Large Text
Manage long concatenated strings:
-- 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;Cross-Database Compatibility
MySQL Alternative
For MySQL users:
-- 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 Alternative
For SQLite users:
-- SQLite using GROUP_CONCAT
SELECT
category,
GROUP_CONCAT(product_name, ', ') AS product_list
FROM products
GROUP BY category;Common Mistakes to Avoid
- Not Handling NULLs Properly
-- Wrong way - NULLs can break concatenation
STRING_AGG(column_name, ', ')
-- Right way - Handle NULLs explicitly
STRING_AGG(COALESCE(column_name, ''), ', ')- Forgetting Order By
-- Wrong way - Undefined order
STRING_AGG(product_name, ', ')
-- Right way - Explicit ordering
STRING_AGG(product_name, ', ' ORDER BY product_name)- Not Setting Maximum Lengths
-- Potential memory issues with large datasets
STRING_AGG(long_text, ' ')
-- Better - Limit output length
LEFT(STRING_AGG(long_text, ' '), 1000)Special Use Cases
JSON Creation
Build JSON arrays from rows:
-- 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"]By understanding these patterns and potential issues, you can effectively use STRING_AGG to create meaningful string aggregations in your SQL queries. Remember to consider performance and data volume when working with large datasets.
