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:
|
1 2 3 4 5 6 7 |
SELECT STUFF((SELECT ', ' + ColumnName FROM TableName FOR XML PATH('')), 1, 2, '') AS CommaSeparatedList; |
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:
|
1 2 3 4 5 |
SELECT STRING_AGG(Genre, ',') AS Result FROM Genres |
Result:
|
1 2 3 4 5 |
Rock,Jazz,Country,Pop,Blues,Hip Hop,Rap,Punk |
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:
|
1 2 3 4 5 6 |
SELECT GROUP_CONCAT(ColumnName SEPARATOR ', ') AS CommaSeparatedList FROM TableName; |
The
SEPARATOR keyword allows you to specify the delimiter for your list.
Example:
|
1 2 3 4 5 6 |
SELECT GROUP_CONCAT(PetName) FROM Pets; |
Result:
|
1 2 3 4 5 |
Fluffy,Fetch,Scratch,Wag,Tweet,Fluffy,Bark,Meow |
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:
|
1 2 3 4 5 6 |
SELECT STRING_AGG(ColumnName, ', ') WITHIN GROUP (ORDER BY ColumnName) AS CommaSeparatedList FROM TableName; |
This query orders the results alphabetically based on
ColumnName before creating the comma-separated list.
Example:
|
1 2 3 4 5 6 |
SELECT STRING_AGG(PetName, ',') FROM Pets; |
Result:
|
1 2 3 4 5 |
Fluffy,Fetch,Scratch,Wag,Tweet,Fluffy,Bark,Meow |
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:
|
1 2 3 4 5 6 |
SELECT LISTAGG(ColumnName, ', ') WITHIN GROUP (ORDER BY ColumnName) AS CommaSeparatedList FROM TableName; |
Just like PostgreSQL, this query orders the results before creating the list.
Example:
|
1 2 3 4 5 6 7 |
SELECT LISTAGG(last_name, ', ') FROM employees WHERE job_id = 'IT_PROG'; |
Result:
|
1 2 3 4 5 |
Hunold, Ernst, Austin, Pataballa, Lorentz |
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:
|
1 2 3 4 5 6 |
SELECT GROUP_CONCAT(ColumnName, ', ') AS CommaSeparatedList FROM (SELECT ColumnName FROM TableName); |
Example:
|
1 2 3 4 5 6 |
SELECT group_concat(FirstName) FROM Employee; |
Result:
|
1 2 3 4 5 |
Andrew,Nancy,Jane,Margaret,Steve,Michael,Robert,Laura |
MariaDB
Like MySQL, MariaDB also has a GROUP_CONCAT() function:
|
1 2 3 4 5 6 |
SELECT GROUP_CONCAT(PetName) FROM Pets; |
Result:
|
1 2 3 4 5 |
Fluffy,Fetch,Scratch,Wag,Tweet,Fluffy,Bark,Meow |
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/
