How do you aggregate values into a single string?

Q: How do you aggregate values into a single string?

**Target Audience:** Beginners who know `GROUP BY` and want to produce "one row with a list", and candidates who need the dialect differences that interviewers love to ask. **Difficulty:** Medium **Engines:** SQLite 3.45.0 (sql.js 1.10.2, the engine the runnable examples on this page use) and PostgreSQL 16.15, both run for this page. MySQL, SQL Server and Oracle syntax is cited from their documentation, not run. > **How to read this doc:** Every idea is explained in plain language first, then tagged with `📌 Interview term:` so you also learn the exact words an interviewer expects. Every result shown was produced by actually running it; the **Verified** section quotes the example output in full. ## 1. Why This Even Matters — A Story First A teacher has a pile of sticky notes, one per student per club: "Asha — chess", "Asha — drama", "Ben — chess". The head asks for a list with one line per student: "Asha: chess, drama". The teacher sorts the notes into piles by student, then writes

The problem

Question presented to candidate: "We have posts and a post_tags table with one row per tag. The blog page wants one row per post with all its tags in a single comma-separated string, like analytics, sql, window. How do you do that in SQL, and what changes between PostgreSQL, MySQL, SQLite and SQL Server?"

What a strong answer should cover:

String aggregation is an aggregate function: it collapses a group of rows into one text value, used with GROUP BY
The names: STRING_AGG(col, sep) in PostgreSQL and SQL Server, GROUP_CONCAT in MySQL and SQLite (SQLite 3.44 also accepts string_agg), LISTAGG in Oracle
Order is not guaranteed unless you say so: ORDER BY inside the aggregate (SQL Server: WITHIN GROUP (ORDER BY ...))
NULL values are skipped; a group with nothing to join gives NULL, so COALESCE it; DISTINCT removes repeats
Limits and traps: MySQL group_concat_max_len (1024 by default) truncates silently; PostgreSQL needs text input (id::text); values containing the separator make the string ambiguous, where a JSON or array aggregate is safer

Code / implementation expected: Yes — a GROUP BY query with STRING_AGG or GROUP_CONCAT and an explicit order.

Schema used by the examples:

string-agg

Try it yourself

Sketch your approach before you read the solution. That is what the interview tests.

Stuck? Get a hint.

A short nudge toward the approach. It will not give the answer away.

Solution

Target Audience: Beginners who know GROUP BY and want to produce "one row with a list", and candidates who need the dialect differences that interviewers love to ask. Difficulty: Medium Engines: SQLite 3.45.0 (sql.js 1.10.2, the engine the runnable examples on this page use) and PostgreSQL 16.15, both run for this page. MySQL, SQL Server and Oracle syntax is cited from their documentation, not run.

How to read this doc: Every idea is explained in plain language first, then tagged with 📌 Interview term: so you also learn the exact words an interviewer expects. Every result shown was produced by actually running it; the Verified section quotes the example output in full.

1. Why This Even Matters — A Story First

A teacher has a pile of sticky notes, one per student per club: "Asha — chess", "Asha — drama", "Ben — chess". The head asks for a list with one lin

The solution is hidden so you can try first

12 min read · 12 sections

Run the code

Edit the query and run it against the tables for this question.

SQLOne row per post with its tags in alphabetical order; posts without tags get a placeholder

SQLSQLite options: no ORDER BY, DISTINCT, the string_agg alias, and NULL tags skipped

PostgreSQL: STRING_AGG with DISTINCT and ORDER BY, next to ARRAY_AGG

SELECT p.id,
       STRING_AGG(DISTINCT t.tag, ', ' ORDER BY t.tag) AS tags,
       ARRAY_AGG(t.tag ORDER BY t.tag) AS tag_array
FROM posts AS p
LEFT JOIN post_tags AS t ON t.post_id = p.id
GROUP BY p.id
ORDER BY p.id;