224

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?

Anonymous
  • 835
  • 1
  • 5
  • 21
Chin
  • 19,717
  • 37
  • 107
  • 164
  • 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

392

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
  • 605,456
  • 145
  • 1,078
  • 1,228
  • 6
    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
  • 4
    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
  • Another note that you can use an optional `DISTINCT` clause that can go into `string_agg` function before the first parameter. e.g. `string_agg(DISTINCT actor, ', ')` – Chad Nov 17 '22 at 17:20
86

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
  • 31,810
  • 31
  • 111
  • 133
  • what is the difference between string~agg and array-agg? – mercury Nov 04 '21 at 16:55
  • 1
    @mercury, string-agg makes field with string type, that is where all elements of string_agg's argument is concatenated into single string, array-agg makes field with type array of, e.g., strings, that is where all elements of array_agg's argument will be of its original type. – Thatislove Oct 15 '22 at 10:09