Concatenate multiple result rows of one column into one, group by another column
aggregate-functions, postgresql, sql
Solution
Simpler with the aggregate function `string_agg()` (Postgres 9.0 or later):
SELECT movie, string_agg(actor, ', ') AS actor_list
FROM tbl
GROUP 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 string types (`varchar`, `character`, `name`, ...) 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_list
FROM tbl
GROUP BY 1;
But it's typically faster to sort rows in a subquery. See:
- Create array in SELECT
Problem
I'm having a table like this Movie Actor A 1 A 2 A 3 B 4 I want to get the name of a movie and all actors in that movie, and I want the result to be in a format like this: Movie ActorList A 1, 2, 3 How can I do it?