postgreSQL Reversing REGEXP_SPLIT TO TABLE

Question

I'm working on an assignment where the question is: "List all of the actor names, semi-colon separated, that acted in the same movie. Your final list should include both the movie title and semi-colon separated actor names"

Currently I have

SELECT  title, name as actor FROM (SELECT *, count(*) OVER (partition by movies.movieid) as count FROM actors 
JOIN movieroles on actors.actorid = movieroles.actorid
JOIN movies on movieroles.movieid = movies.movieid)X 
WHERE X.count>1
GROUP BY title, actor;

which outputs

               title                |         actor
------------------------------------+-----------------------
 The Terminator                     | Arnold Schwarzenegger
 The Terminator                     | Michael Biehn
 Sherlock Holmes: A Game of Shadows | Jude Law
 The Terminator                     | Linda Hamilton
 Sherlock Holmes: A Game of Shadows | Rachel McAdams

However, I need to produce

               "title"                                        "actor"                        
------------------------------------------------------------------------------
 Sherlock Holmes: A Game of Shadows   Jude Law;Rachel McAdams
 The Terminator                       Linda Hamilton;Michael Biehn;Arnold Schwarzenegger

So my question is how would one approach reversing the split_part or a similar function of sorts to give desired output?

Barbaros Özhan · Accepted Answer · 2020-05-15 23:00:25Z

0

Just STRING_AGG() function is needed with ; argument as a separator with grouping by title column :

SELECT title, STRING_AGG(DISTINCT name, ';') as actors 
  FROM actors a
  JOIN movieroles mr ON a.actorid = mr.actorid
  JOIN movies m ON m.movieid = mr.movieid
 GROUP BY title;

and an optional order by clause might be used

( syntax : STRING_AGG ( expression, separator [order_by_clause] ) ) .

Aliasing tables would be elegant for readability of the query as a later reference.

edited May 15, 2020 at 23:00

answered May 15, 2020 at 22:53

Barbaros Özhan

65.9k11 gold badges36 silver badges64 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user13022048 Over a year ago

Thank you! got the output I need and just in time!

Collectives™ on Stack Overflow

postgreSQL Reversing REGEXP_SPLIT TO TABLE

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related