Command guide

advanced aggregation MySQL MariaDB SQL Server PostgreSQL SQLite

Concatenate Values Within A Group

Aggregate multiple string values from grouped rows into a single delimited string.

5/5 supported engines validation-green 1 example 2 scenarios

Docker-validated Not currently validation-green

Example 1

List each customer's ordered products as a single string

Each customer's products are sorted alphabetically within the group before being joined. Alice's two products become 'Keyboard, Mouse'; Bob's become 'Keyboard, Monitor'. Carol has a single product so no separator appears.

MySQL MariaDB

Engine-specific syntax

Source table data

Setup

CREATE TABLE orders (id INT, customer VARCHAR(50), product VARCHAR(50));

INSERT INTO
  orders (id, customer, product)
VALUES
  (1, 'Alice', 'Keyboard'),
  (2, 'Alice', 'Mouse'),
  (3, 'Bob', 'Monitor'),
  (4, 'Bob', 'Keyboard'),
  (5, 'Carol', 'Mouse');

Validated query

SQL

SELECT
  customer,
  GROUP_CONCAT (
    product
    ORDER BY
      product SEPARATOR ', '
  ) AS products
FROM
  orders
GROUP BY
  customer
ORDER BY
  customer;

Expected result

customer	products
Alice	Keyboard, Mouse
Bob	Keyboard, Monitor
Carol	Mouse

SQL Server

Engine-specific syntax

Source table data

Setup

CREATE TABLE orders (id INT, customer VARCHAR(50), product VARCHAR(50));

INSERT INTO
  orders (id, customer, product)
VALUES
  (1, 'Alice', 'Keyboard'),
  (2, 'Alice', 'Mouse'),
  (3, 'Bob', 'Monitor'),
  (4, 'Bob', 'Keyboard'),
  (5, 'Carol', 'Mouse');

Validated query

SQL

SELECT
  customer,
  STRING_AGG (product, ', ') WITHIN GROUP (
    ORDER BY
      product
  ) AS products
FROM
  orders
GROUP BY
  customer
ORDER BY
  customer;

Expected result

customer	products
Alice	Keyboard, Mouse
Bob	Keyboard, Monitor
Carol	Mouse

PostgreSQL

Engine-specific syntax

Source table data

Setup

CREATE TABLE orders (id INT, customer VARCHAR(50), product VARCHAR(50));

INSERT INTO
  orders (id, customer, product)
VALUES
  (1, 'Alice', 'Keyboard'),
  (2, 'Alice', 'Mouse'),
  (3, 'Bob', 'Monitor'),
  (4, 'Bob', 'Keyboard'),
  (5, 'Carol', 'Mouse');

Validated query

SQL

SELECT
  customer,
  STRING_AGG (
    product,
    ', '
    ORDER BY
      product
  ) AS products
FROM
  orders
GROUP BY
  customer
ORDER BY
  customer;

Expected result

customer	products
Alice	Keyboard, Mouse
Bob	Keyboard, Monitor
Carol	Mouse

SQLite

Engine-specific syntax

Source table data

Setup

CREATE TABLE orders (id INT, customer VARCHAR(50), product VARCHAR(50));

INSERT INTO
  orders (id, customer, product)
VALUES
  (1, 'Alice', 'Keyboard'),
  (2, 'Alice', 'Mouse'),
  (3, 'Bob', 'Monitor'),
  (4, 'Bob', 'Keyboard'),
  (5, 'Carol', 'Mouse');

Validated query

SQL

SELECT
  customer,
  GROUP_CONCAT (product, ', ') AS products
FROM
  (
    SELECT
      customer,
      product
    FROM
      orders
    ORDER BY
      customer,
      product
  ) sub
GROUP BY
  customer
ORDER BY
  customer;

Expected result

customer	products
Alice	Keyboard, Mouse
Bob	Keyboard, Monitor
Carol	Mouse

MySQL and MariaDB use GROUP_CONCAT; PostgreSQL and SQL Server use STRING_AGG. Results are identical.

Useful when

Where this command helps.

building a comma-separated list of items per customer or category
collapsing a one-to-many relationship into a single readable column

Explanation

What the command is doing.

String aggregation collapses multiple rows into a comma-separated list within a GROUP BY. MySQL and MariaDB use GROUP_CONCAT, while PostgreSQL and SQL Server use STRING_AGG. Both support ordering the values within each group before concatenation.

Concatenate Values Within A Group

List each customer's ordered products as a single string

Where this command helps.

What the command is doing.

Keep moving through nearby tasks.