188

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?

Himanshu Jansari
  • 30,115
  • 28
  • 106
  • 129
Chin
  • 18,166
  • 35
  • 100
  • 154
  • then check this: http://stackoverflow.com/questions/12365467/postgresql-9-1-how-to-concatenate-rows-in-array-without-duplicates-join-anothe – Asken Apr 06 '13 at 05:04

2 Answers2

331

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:

Erwin Brandstetter
  • 539,169
  • 125
  • 977
  • 1,137
  • 4
    I didn't know Postgres supported positional column references like that, and can't think of any good reason to use them, but otherwise this is spot on. – IMSoP Apr 06 '13 at 11:51
  • 1
    @IMSoP: It's just a syntactical convenience I slipped in. A good use case would be a complex expressions in the `SELECT` list or with dynamic SQL. – Erwin Brandstetter Apr 06 '13 at 11:54
  • 2
    Small note - might need to `actor::TEXT` if `actor` is an `INT`. At least, I get an error trying to `string_agg` `INT`s in Postgres 9.5 - but otherwise, this was exactly what I needed, thanks! – dwanderson Dec 13 '16 at 16:26
  • @dwanderson: I added a note accordingly. – Erwin Brandstetter Dec 13 '16 at 17:03
  • The above works but I get (...) on longer strings :( – Chris Jun 02 '17 at 18:44
  • 1
    @Chris: Probably an issue with your client settings, unrelated to the query. Consider: https://stackoverflow.com/a/23568429/939860 – Erwin Brandstetter Jun 02 '17 at 21:17
  • 3
    Worth noting that an optional `ORDER BY` clause can go into the `string_agg` function after the delimiter argument, e.g. `string_agg(actor, ', ' ORDER BY actor DESC)` – isapir Oct 23 '17 at 20:58
  • late comment but a quick note on this nice, succinct solution. I found that this was actually consistently slower than the solution from @hims056 for my use case. (avg. 280ms vs 200ms). I'm a SQL/PostgreSQL novice and haven't been able to set up a SQLFiddle or similar, I'm afraid. Do you know of any obvious reason? – bigsee Jan 03 '19 at 16:04
  • dear @ErwinBrandstetter, is there possibility to transform second value not as string but as a json? – Pyrejkee Sep 19 '19 at 07:55
  • @Pyrejkee: Certainly. Here are a couple of options: https://stackoverflow.com/a/26486910/939860 – Erwin Brandstetter Sep 19 '19 at 15:41
76

You can use array_agg function for that:

SELECT "Movie",
array_to_string(array_agg(distinct "Actor"),',') AS Actor
FROM Table1
GROUP BY "Movie";

Result:

MOVIE ACTOR
A 1,2,3
B 4

See this SQLFiddle

For more See 9.18. Aggregate Functions

Himanshu Jansari
  • 30,115
  • 28
  • 106
  • 129