AI Hints
Homeโ€บSQL Tutorialโ€บPivots, Lists, and Recursion
SQL ยท Lesson 109 of 126

Lists in One Cell

GROUP_CONCAT.

โฑ 2 min readโ–ถ Video at 2:34:04๐ŸŽ“ Free
Watch this lesson in the SQL courseWatch on YouTube โ†—Full SQL course

Sometimes you want a list, not a number. Which cities do our customers live in, for each country? Group by would give one row per city. But Helen wants one row per country, with the cities listed together.

Lists in One Cell
From the video: Lists in One Cell
  • A list, not a number
  • Cities per country
  • One row each

GROUP_CONCAT

Group concat is an aggregate function, like count. But instead of counting, it joins the values together with commas. Distinct removes repeats. Run it. Each country, with all its cities in one cell.

query.sqlsql
SELECT country,
       GROUP_CONCAT(DISTINCT city) AS cities
FROM customers
WHERE city IS NOT NULL
GROUP BY country;
Output
โ”Œโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ฌโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”
โ”‚  country  โ”‚         cities          โ”‚
โ”œโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ผโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ค
โ”‚ Australia โ”‚ Melbourne,Sydney        โ”‚
โ”‚ Canada    โ”‚ Toronto,Vancouver       โ”‚
โ”‚ Germany   โ”‚ Berlin                  โ”‚
โ”‚ Ireland   โ”‚ Dublin                  โ”‚
โ”‚ UK        โ”‚ Manchester,Leeds,London โ”‚
โ”‚ USA       โ”‚ Chicago,New York,Austin โ”‚
โ””โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ดโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”˜

Choosing the separator

You can choose the separator, as a second argument. Here we use a vertical bar, to list each author's books. Run it. Kai Lund wrote all three fantasy books. In Postgres and SQL Server, the same function is called string ag.

query.sqlsql
SELECT author,
       COUNT(*) AS books,
       GROUP_CONCAT(title, ' | ') AS titles
FROM books
WHERE genre IN ('Fantasy', 'Mystery')
GROUP BY author;
Output
โ”Œโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ฌโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ฌโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”
โ”‚   author   โ”‚ books โ”‚                             titles                             โ”‚
โ”œโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ผโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ผโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ค
โ”‚ Kai Lund   โ”‚ 3     โ”‚ Dragons of Ember Bay | Crown of Ash | The Glass Forest         โ”‚
โ”‚ Mira Hale  โ”‚ 3     โ”‚ The Silent Orchard | The Last Lighthouse | Murder at Mill Lane โ”‚
โ”‚ Nora Wells โ”‚ 1     โ”‚ The Painted Door                                               โ”‚
โ””โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ดโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ดโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”˜

Summary

Group concat joins a group's values into one cell. Handy for reports, and emails. Next, a query that calls itself.

Key takeaways
  • GROUP_CONCAT(col)
  • Choose a separator
  • STRING_AGG elsewhere
Next up: Recursive CTEs โ€” a query that repeats