Most of the major RDBMSs have a function that enables us to return our query results as a comma separated list.

That is, we can use such a function to convert each row into a separate list item, within a comma separated list.

Below are examples of how to achieve this in some of the more popular RDBMSs.

MySQL

MySQL has the GROUP_CONCAT() function that allows us to output our query results in a comma separated list:

SELECT GROUP_CONCAT(PetName) 
FROM Pets;

Result:

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.

See GROUP_CONCAT() Function in MySQL for more examples.

Oracle Database

Oracle Database has the LISTAGG() function:

SELECT LISTAGG(last_name, ', ')
FROM employees
WHERE job_id = 'IT_PROG';

Result:

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.

See LISTAGG() Function in Oracle for more examples.

SQL Server

SQL Server has the STRING_AGG() function to return our results in a comma separated list:

SELECT STRING_AGG(Genre, ',') AS Result FROM Genres

Result:

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.

See How to Return Query Results as a Comma Separated List in SQL Server for more examples.

MariaDB

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

SELECT GROUP_CONCAT(PetName) 
FROM Pets;

Result:

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.

See MariaDB GROUP_CONCAT() for more examples.

PostgreSQL

Postgres has the STRING_AGG() function:

SELECT STRING_AGG(PetName, ',') 
FROM Pets;

Result:

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.

See STRING_AGG() Function in PostgreSQL for more examples.

SQLite

In SQLite, we can use the GROUP_CONCAT() function to transform our query results to a comma separated list:

SELECT group_concat(FirstName)
FROM Employee;

Result:

Andrew,Nancy,Jane,Margaret,Steve,Michael,Robert,Laura

See How GROUP_CONCAT() Works in SQLite for more examples.

Multiple Columns

The above examples all use a single column for the list. We can also concatenate multiple columns to produce a list that includes multiple columns.

Suppose we have a table with the following data:

SELECT TaskId, TaskName 
FROM Tasks;

Result:

TaskId  TaskName    
------  ------------
1       Feed cats   
2       Water dog   
3       Feed garden 
4       Paint carpet
5       Clean roof  
6       Feed cats   

In SQL Server we can do the following to output both columns in a single row:

SELECT STRING_AGG(CONCAT(TaskId, ') ', TaskName), ' ') 
FROM Tasks

Result:

1) Feed cats 2) Water dog 3) Feed garden 4) Paint carpet 5) Clean roof 6) Feed cats