How to combine rows into one string in SQL

CREATE TABLE staff (
    id   INTEGER PRIMARY KEY,
    dept TEXT,
    name TEXT
);
INSERT INTO staff (dept, name) VALUES
    ('Sales', 'Maya'),
    ('IT', 'Omar'),
    ('Sales', 'Liam'),
    ('IT', 'Ava'),
    ('HR', 'Zoe'),
    ('Sales', 'Ella');

-- One row per department, its names joined with ', '
SELECT dept,
       count(*) AS n,
       string_agg(name, ', ') AS names
FROM staff
GROUP BY dept
ORDER BY dept;

-- group_concat: SQLite's older name, and MySQL's (default separator ',')
SELECT dept, group_concat(name) AS names
FROM staff
GROUP BY dept
ORDER BY dept;

CREATE TABLE staff (dept TEXT, name TEXT, hired TEXT);
INSERT INTO staff VALUES
    ('Sales', 'Maya', '2021-03-01'),
    ('IT', 'Omar', '2024-06-10'),
    ('Sales', 'Liam', '2025-01-15'),
    ('IT', 'Ava', '2019-09-02'),
    ('HR', 'Zoe', '2023-04-20'),
    ('Sales', 'Ella', '2022-11-30');

-- Alphabetical inside each list (SQLite 3.44+)
SELECT dept, string_agg(name, ', ' ORDER BY name) AS names
FROM staff
GROUP BY dept
ORDER BY dept;

-- Sort by a column you don't show: most recent hire first
SELECT dept, string_agg(name, ', ' ORDER BY hired DESC) AS newest
FROM staff
GROUP BY dept
ORDER BY dept;

-- The outer ORDER BY sorts the groups, not the names
SELECT dept, group_concat(name, ' / ' ORDER BY name) AS names
FROM staff
GROUP BY dept
ORDER BY count(*) DESC, dept;

CREATE TABLE orders (customer TEXT, item TEXT);
INSERT INTO orders VALUES
    ('Ana', 'tea'), ('Ana', 'cake'), ('Ana', 'tea'),
    ('Ben', 'coffee'), ('Ben', 'coffee'), ('Ben', 'tea');

-- Every row counts, repeats included
SELECT customer, string_agg(item, ', ') AS items
FROM orders
GROUP BY customer;

-- DISTINCT drops repeats, but only with the default ',' separator
SELECT customer, group_concat(DISTINCT item ORDER BY item) AS items
FROM orders
GROUP BY customer;

-- Any separator: remove the duplicates in a subquery first
SELECT customer, string_agg(item, ', ' ORDER BY item) AS items
FROM (SELECT DISTINCT customer, item FROM orders)
GROUP BY customer;

-- SQLite rejects DISTINCT together with a separator
SELECT string_agg(DISTINCT item, ', ') FROM orders;

CREATE TABLE tasks (project TEXT, owner TEXT);
INSERT INTO tasks VALUES
    ('Site', 'Maya'), ('Site', NULL), ('Site', 'Omar'),
    ('App', NULL), ('App', NULL),
    ('Docs', 'Ava');

-- NULLs are skipped; a group with only NULLs gives NULL
SELECT project, count(*) AS tasks, string_agg(owner, ', ') AS owners
FROM tasks
GROUP BY project
ORDER BY project;

-- Replace the NULL result, or keep a placeholder per NULL row
SELECT project,
       coalesce(string_agg(owner, ', '), '-') AS owners,
       string_agg(coalesce(owner, '?'), ', ') AS with_gaps
FROM tasks
GROUP BY project
ORDER BY project;

-- || with a NULL is NULL, so those rows vanish from the list too
SELECT string_agg(project || '=' || owner, ', ') AS pairs
FROM tasks;

CREATE TABLE customers (id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE orders (customer_id INTEGER, item TEXT);
INSERT INTO customers VALUES (1, 'Cy'), (2, 'Ana'), (3, 'Ben');
INSERT INTO orders VALUES (2, 'tea'), (3, 'coffee'), (2, 'cake'), (3, 'tea');

-- No GROUP BY: the whole table becomes one row
SELECT string_agg(name, ', ' ORDER BY name) AS everyone
FROM customers;

-- Each value quoted, ready for an IN (...) list
SELECT string_agg('''' || name || '''', ', ') AS quoted
FROM customers;

-- A list per customer from a second table; LEFT JOIN keeps Cy
SELECT c.name, string_agg(o.item, ', ') AS items
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
GROUP BY c.id, c.name
ORDER BY c.name;

-- The same with a subquery in the SELECT list
SELECT name,
       (SELECT string_agg(item, ', ')
        FROM orders
        WHERE customer_id = customers.id) AS items
FROM customers
ORDER BY name;