CODEWITHSUNDEEP
sqlpostgresqlpostgresql-9.1
Daud
Daud

Reputation: 7877

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

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 ?

Upvotes: 7

Views: 13069

Answers (1)

John Woo
John Woo

Reputation: 263683

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

Upvotes: 18

Related Questions