By Chin


2013-04-06 05:00:27 8 Comments

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?

2 comments

@Erwin Brandstetter 2013-04-06 11:44:03

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 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_list
FROM   tbl
GROUP  BY 1;

But it's typically faster to sort rows in a subquery. See:

@IMSoP 2013-04-06 11:51:29

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.

@Erwin Brandstetter 2013-04-06 11:54:46

@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.

@dwanderson 2016-12-13 16:26:55

Small note - might need to actor::TEXT if actor is an INT. At least, I get an error trying to string_agg INTs in Postgres 9.5 - but otherwise, this was exactly what I needed, thanks!

@Erwin Brandstetter 2016-12-13 17:03:48

@dwanderson: I added a note accordingly.

@Chris 2017-06-02 18:44:56

The above works but I get (...) on longer strings :(

@Erwin Brandstetter 2017-06-02 21:17:01

@Chris: Probably an issue with your client settings, unrelated to the query. Consider: stackoverflow.com/a/23568429/939860

@isapir 2017-10-23 20:58:53

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)

@bigsee 2019-01-03 16:04:59

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?

@Pyrejkee 2019-09-19 07:55:57

dear @ErwinBrandstetter, is there possibility to transform second value not as string but as a json?

@Erwin Brandstetter 2019-09-19 15:41:37

@Pyrejkee: Certainly. Here are a couple of options: stackoverflow.com/a/26486910/939860

@hims056 2013-04-06 05:10:06

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

Related Questions

Sponsored Content

16 Answered Questions

[SOLVED] Select first row in each GROUP BY group?

22 Answered Questions

[SOLVED] Get list of all tables in Oracle?

  • 2008-10-15 17:54:49
  • vitule
  • 2160534 View
  • 1106 Score
  • 22 Answer
  • Tags:   sql oracle

32 Answered Questions

[SOLVED] Finding duplicate values in a SQL table

  • 2010-04-07 18:17:29
  • Alex
  • 2849776 View
  • 1949 Score
  • 32 Answer
  • Tags:   sql duplicates

24 Answered Questions

[SOLVED] How can I drop all the tables in a PostgreSQL database?

  • 2010-07-24 23:24:05
  • AP257
  • 691983 View
  • 1068 Score
  • 24 Answer
  • Tags:   postgresql

11 Answered Questions

[SOLVED] Can I concatenate multiple MySQL rows into one field?

2 Answered Questions

[SOLVED] Using group by on multiple columns

47 Answered Questions

Sponsored Content