Convert Multiple Rows into a Comma Separated List in SQL

Example of a function to convert each row into a separate list item, within a comma separated list.

Avatar

Many of the major Relational Database Management Systems (RDBMSs) offer a valuable function that allows users to return query results as a comma-separated list. This feature proves exceptionally useful when there’s a need to transform each row in the result set into individual list items within a comma-separated list. This capability streamlines data retrieval and manipulation, making it a common and essential tool for many database professionals and developers. Let’s explore how to achieve this in several popular RDBMSs.

 

SQL Server

In Microsoft SQL Server, you can use the FOR XML PATH clause to concatenate values into a comma-separated list. Here’s an example query:

This query uses the STUFF function to remove the leading comma and space resulting from the concatenation.

Or you can also other solution, SQL Server has the STRING_AGG() function to return our results in a comma separated list:

Result:

We can also remove duplicate values, specify an order for the results, change the delimiter, etc.

 

MySQL

MySQL provides the GROUP_CONCAT function to achieve this. Here’s how you can use it:


The SEPARATOR keyword allows you to specify the delimiter for your list.

Example:

Result:

We can also do things like, remove duplicate values (with the DISTINCT clause), specify an order for the results (with the ORDER BY clause), and specify a different delimiter.

 

PostgreSQL

In PostgreSQL, you can use the STRING_AGG function:


This query orders the results alphabetically based on ColumnName before creating the comma-separated list.

Example:

Result:

We can also remove duplicate values (with the DISTINCT clause), specify an order for the results (with the ORDER BY clause), change the separator, etc.

 

Oracle Database

Oracle offers the LISTAGG function for this purpose:


Just like PostgreSQL, this query orders the results before creating the list.

Example:

Result:

Like MySQL, Oracle Database also allows us to remove duplicate values, specify an order for the results, specify a different separator, etc.

 

SQLite

SQLite doesn’t have a built-in function for this, but you can use a combination of GROUP_CONCAT and a subquery to achieve the same result:

Example:

Result:

 

MariaDB

Like MySQL, MariaDB also has a GROUP_CONCAT() function:

Result:

Like MySQL’s function of the same name, we can also do things like, remove duplicate values (with the DISTINCT clause), specify an order for the results (with the ORDER BY clause), change the separator, etc.

However, one thing that MariaDB has over MySQL is the LIMIT clause, which provides us with the ability to limit the number of results in the list.

 

 

These are just a few examples of how to return query results as comma-separated lists in some of the most popular RDBMSs. This functionality simplifies data extraction and manipulation, making it easier to work with your database. Depending on your specific database system, you can choose the appropriate method to suit your needs.

 

Source: https://database.guide/how-to-get-multiple-rows-into-a-comma-separated-list-in-sql/

Leave a Reply

Your email address will not be published. Required fields are marked *