Posted By: Anonymous
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?
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;
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 (
"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_list FROM tbl GROUP BY 1;
But it’s typically faster to sort rows in a subquery. See: