In Postgres, we can use the STRING_AGG() function to transform our query results into a comma separated list.

Instead of each value being output in a separate row (as with any regular query), the values are output to a single row, separated by a comma (or some other delimiter of our choosing).

Example

Imagine we run the following query:

SELECT * 
FROM Genres;

Result:

+---------+---------+
| genreid |  genre  |
+---------+---------+
|       1 | Rock    |
|       2 | Jazz    |
|       3 | Country |
|       4 | Pop     |
|       5 | Blues   |
|       6 | Hip Hop |
|       7 | Rap     |
|       8 | Punk    |
+---------+---------+
(8 rows)

We can run the following query to output the genre column as a comma separated list:

SELECT STRING_AGG(genre, ', ')
FROM Genres;

Result:

+-----------------------------------------------------+
|                     string_agg                      |
+-----------------------------------------------------+
| Rock, Jazz, Country, Pop, Blues, Hip Hop, Rap, Punk |
+-----------------------------------------------------+
(1 row)

We can even include the GenreId column in our list if we want:

SELECT STRING_AGG(CONCAT(GenreId, ') ', Genre), ' ' ORDER BY GenreId ASC) 
FROM Genres;

Result:

1) Rock 2) Jazz 3) Country 4) Pop 5) Blues 6) Hip Hop 7) Rap 8) Punk

Here, I also used the ORDER BY clause within the STRING_AGG() function in order to sort the results by GenreId in ascending order.

We can also do things like, remove duplicates (with the DISTINCT clause), use a different separator, and use the function within a grouped query.

See STRING_AGG() Function in PostgreSQL for examples.