SQL GROUP_CONCAT / STRING_AGG: Concatenate Grouped Values (All Databases)

Overview

Combine multiple row values into a single comma-separated string. Every database has this function but calls it something different.

MySQL

-- GROUP_CONCAT:
SELECT department,
  GROUP_CONCAT(name ORDER BY name SEPARATOR ', ') AS members
FROM employees
GROUP BY department;
-- Engineering: 'Alice, Bob, Charlie'

-- With DISTINCT:
SELECT GROUP_CONCAT(DISTINCT category ORDER BY category) FROM products;

-- Custom separator:
SELECT GROUP_CONCAT(tag SEPARATOR ' | ') FROM post_tags
WHERE post_id = 1;

-- Increase max length (default 1024):
SET SESSION group_concat_max_len = 100000;

PostgreSQL

-- STRING_AGG(expression, separator):
SELECT department,
  STRING_AGG(name, ', ' ORDER BY name) AS members
FROM employees
GROUP BY department;

-- With DISTINCT:
SELECT STRING_AGG(DISTINCT category, ', ' ORDER BY category)
FROM products;

-- ARRAY_AGG (returns array instead of string):
SELECT department, ARRAY_AGG(name ORDER BY name) AS members
FROM employees
GROUP BY department;

SQL Server

-- STRING_AGG (SQL Server 2017+):
SELECT department,
  STRING_AGG(name, ', ') WITHIN GROUP (ORDER BY name) AS members
FROM employees
GROUP BY department;

-- Older versions — FOR XML PATH:
SELECT department,
  STUFF((
    SELECT ', ' + name
    FROM employees e2
    WHERE e2.department = e1.department
    ORDER BY name
    FOR XML PATH('')
  ), 1, 2, '') AS members
FROM employees e1
GROUP BY department;

Oracle

-- LISTAGG:
SELECT department,
  LISTAGG(name, ', ') WITHIN GROUP (ORDER BY name) AS members
FROM employees
GROUP BY department;

-- With DISTINCT (Oracle 19c+):
SELECT LISTAGG(DISTINCT category, ', ')
  WITHIN GROUP (ORDER BY category)
FROM products;

-- Overflow handling (Oracle 12c R2+):
SELECT LISTAGG(name, ', ' ON OVERFLOW TRUNCATE '...')
  WITHIN GROUP (ORDER BY name)
FROM employees;

Skip the Syntax Lookup

Instead of memorizing GROUP_CONCAT / STRING_AGG syntax for each database, describe what you need in plain English and let AI2SQL generate the correct query.

Frequently Asked Questions