How to Concatenate Rows into a Single String in SQL

Posted by Kyle Hankinson January 22, 2025

---

A common SQL task is combining values from multiple rows into a single comma-separated string. For example, listing all tags for a blog post or all products in an order. Every database has a function for this, but the syntax differs.

MySQL: GROUP_CONCAT

SELECT 
    order_id,
    GROUP_CONCAT(product_name ORDER BY product_name SEPARATOR ', ') AS products
FROM order_items
GROUP BY order_id;

Key options:

• SEPARATOR sets the delimiter (default is comma)
• ORDER BY controls the sort order within the string
• DISTINCT removes duplicate values

-- Unique categories, pipe-separated
SELECT GROUP_CONCAT(DISTINCT category ORDER BY category SEPARATOR ' | ')
FROM products;

Watch out: MySQL's group_concat_max_len defaults to 1024 bytes. For longer results:

SET SESSION group_concat_max_len = 100000;

PostgreSQL: STRING_AGG

SELECT 
    order_id,
    STRING_AGG(product_name, ', ' ORDER BY product_name) AS products
FROM order_items
GROUP BY order_id;

PostgreSQL's syntax puts the ORDER BY inside the function call. DISTINCT is also supported:

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

SQL Server: STRING_AGG (2017+)

SELECT 
    order_id,
    STRING_AGG(product_name, ', ') WITHIN GROUP (ORDER BY product_name) AS products
FROM order_items
GROUP BY order_id;

SQL Server uses WITHIN GROUP for ordering rather than putting ORDER BY inside the parentheses.

SQL Server 2016 and Earlier: FOR XML PATH

Before STRING_AGG was available, the standard workaround was:

SELECT 
    o.order_id,
    STUFF((
        SELECT ', ' + product_name
        FROM order_items oi
        WHERE oi.order_id = o.order_id
        ORDER BY product_name
        FOR XML PATH('')
    ), 1, 2, '') AS products
FROM orders o;

This is verbose but works on older SQL Server versions.

Oracle: LISTAGG

SELECT 
    order_id,
    LISTAGG(product_name, ', ') WITHIN GROUP (ORDER BY product_name) AS products
FROM order_items
GROUP BY order_id;

Oracle's syntax is similar to SQL Server's STRING_AGG.

Note: In Oracle 12c Release 2+, you can add ON OVERFLOW TRUNCATE to handle strings that exceed the VARCHAR2 length limit:

LISTAGG(product_name, ', ' ON OVERFLOW TRUNCATE '...')
    WITHIN GROUP (ORDER BY product_name)

SQLite: GROUP_CONCAT

SQLite uses the same function name as MySQL:

SELECT order_id, GROUP_CONCAT(product_name, ', ')
FROM order_items
GROUP BY order_id;

SQLite's version does not support ORDER BY within the function or a custom separator syntax — the delimiter is the second argument.

Quick Reference

Database	Function	Ordering
MySQL	GROUP_CONCAT(col SEPARATOR ', ')	ORDER BY inside function
PostgreSQL	STRING_AGG(col, ', ')	ORDER BY inside function
SQL Server 2017+	STRING_AGG(col, ', ')	WITHIN GROUP (ORDER BY ...)
SQL Server < 2017	FOR XML PATH trick	ORDER BY in subquery
Oracle	LISTAGG(col, ', ')	WITHIN GROUP (ORDER BY ...)
SQLite	GROUP_CONCAT(col, ', ')	Not supported

Using SQLPro Studio

SQLPro Studio supports MySQL, PostgreSQL, SQL Server, Oracle, and SQLite — so whichever syntax you need, you can write and test it in the same app. The syntax highlighting understands all of these functions.

---