how to collect multiple values as a single string in postgres?

postgresql, postgresql-9.1, sql

Solution

You can join the tables and use `array_agg` to combine the values separated by a comma

SELECT a.id, array_agg(b.name) assignments
FROM    Project a
        INNER JOIN assignment b
          ON a.id = b.project_ID
GROUP BY a.id

SQLFiddle Demo

or by using `STRING_AGG`

SELECT a.id, STRING_AGG(b.name, ', ' ORDER BY b.name) assignments
FROM    Project a
        INNER JOIN assignment b
          ON a.id = b.project_ID
GROUP BY a.id

SQLFiddle Demo

Problem

I have tables : ``` Project table id name ------- 1 A 2 B Assignment table id name project_id ------------------- 1 A1 1 2 A2 1 3 A3 2 ``` I wish to write a query that returns each project with the name of the assignments created from it, like : ``` project_id assignments ----------------------- 1 A1,A2 2 A3 ``` Is there any way to achieve that ?

Original source