How to Convert SQL Rows to a Comma-Delimited String in various RDBMSs

Updated: at 08:40 PM

Working with relational databases often requires you to transform rows into a more user-friendly format. One common task is converting rows into a comma-delimited string. Whether you’re generating reports, feeding data to an API, or simply doing quick text transformations, knowing how to turn multiple rows into a single comma-separated value (CSV) string can be a powerful tool in your SQL arsenal.

In this article, we will explore how to achieve this in MySQL, Oracle, SQL Server, PostgreSQL, and SQLite. By the end of this guide, you’ll have a clear understanding of how each RDBMS handles this conversion and how to use native functions or clever SQL techniques to get the job done.

1. MySQL

In MySQL, converting rows to a comma-delimited string is straightforward using the GROUP_CONCAT function. This function allows you to concatenate values from multiple rows into a single string, separating them by commas (or any delimiter of your choice).

Example:

SELECT GROUP_CONCAT(column_name SEPARATOR ', ') 
FROM table_name;

Explanation:

Use Case:

Let’s assume we have a table employees and we want to list the first names of all employees in a single string:

SELECT GROUP_CONCAT(first_name SEPARATOR ', ') 
FROM employees;

This would return a result like:

Alice, Bob, Charlie

2. Oracle

Oracle doesn’t have a native equivalent to MySQL’s GROUP_CONCAT, but you can achieve the same result using the LISTAGG function, which has similar functionality.

Example:

SELECT LISTAGG(column_name, ', ') WITHIN GROUP (ORDER BY column_name)
FROM table_name;

Explanation:

Use Case:

If we want to get a comma-delimited list of employee names from an employees table, we can write:

SELECT LISTAGG(first_name, ', ') WITHIN GROUP (ORDER BY first_name)
FROM employees;

The output would be:

Alice, Bob, Charlie

3. SQL Server

In SQL Server, we can use the STRING_AGG function (introduced in SQL Server 2017) to concatenate row values into a single string.

Example:

SELECT STRING_AGG(column_name, ', ')
FROM table_name;

Explanation:

Use Case:

For a comma-separated list of employee first names:

SELECT STRING_AGG(first_name, ', ')
FROM employees;

The result will be similar to other databases:

Alice, Bob, Charlie

Legacy SQL Server:

Before SQL Server 2017, you could use FOR XML PATH as a workaround to achieve similar functionality:

SELECT STUFF(
    (SELECT ', ' + first_name 
     FROM employees 
     FOR XML PATH('')), 
    1, 2, ''
);

4. PostgreSQL

PostgreSQL provides the string_agg function, which works similarly to GROUP_CONCAT in MySQL.

Example:

SELECT string_agg(column_name, ', ')
FROM table_name;

Explanation:

Use Case:

To list employee names in a comma-delimited string:

SELECT string_agg(first_name, ', ')
FROM employees;

This will return the familiar output:

Alice, Bob, Charlie

5. SQLite

In SQLite, the function GROUP_CONCAT is available to concatenate rows into a comma-separated string.

Example:

SELECT GROUP_CONCAT(column_name, ', ')
FROM table_name;

Explanation:

Use Case:

For a comma-separated list of first names from the employees table:

SELECT GROUP_CONCAT(first_name, ', ')
FROM employees;

You’ll get the result:

Alice, Bob, Charlie

Conclusion

In this article, we explored how to convert rows into a comma-delimited string across five popular RDBMSs: MySQL, Oracle, SQL Server, PostgreSQL, and SQLite. Each of these databases has built-in functionality to concatenate row values into a single string, but the exact syntax and functions vary.

Here’s a quick recap of the key functions for each database:

Mastering these functions allows you to perform efficient text aggregation, whether you’re building reports, generating CSV files, or preparing data for export.

Next Post
How to Implement CREATE TABLE IF NOT EXISTS in various RDBMSs