In PostgreSQL, how to turn multiple rows of one column into a comma separated string (with the help of string_agg)¶

Ordinarily, PostgreSQL return the query result as a table. But sometimes, we may want a single string that contains data of multiple rows.

An ordinary query example¶

Let's make a SQL query:

SELECT name FROM company;

The output is:

 name
-------
 Paul
 Allen
 Teddy
 Mark
 David
(5 rows)

In the example above, we get 5 rows of the name column.

Turn multiple rows of one column into a comma separated string¶

This time, we use string_agg to make another SQL query:

SELECT string_agg(name, ',') from company;

The output is:

         string_agg
-----------------------------
 Paul,Allen,Teddy,Mark,David
(1 row)

As we can see, the result Paul,Allen,Teddy,Mark,David is the comma separated string we want.