Concatenate multiple result rows of one column into one, group by another column [duplicate]
Simpler with the aggregate function string_agg() (Postgres 9.0 or later):
SELECT movie, string_agg(actor, ', ') AS actor_listFROM tblGROUP BY 1;The 1 in GROUP BY 1 is a positional reference and a shortcut for GROUP BY movie in this case.
string_agg() expects data type text as input. Other types need to be cast explicitly (actor::text) - unless an implicit cast to text is defined - which is the case for all other character types (varchar, character, "char"), and some other types.
As isapir commented, you can add an ORDER BY clause in the aggregate call to get a sorted list - should you need that. Like:
SELECT movie, string_agg(actor, ', ' ORDER BY actor) AS actor_listFROM tblGROUP BY 1;But it's typically faster to sort rows in a subquery. See:
You can use array_agg function for that:
SELECT "Movie",array_to_string(array_agg(distinct "Actor"),',') AS ActorFROM Table1GROUP BY "Movie";Result:
| MOVIE | ACTOR |
|---|---|
| A | 1,2,3 |
| B | 4 |
See this SQLFiddle
For more See 9.18. Aggregate Functions