Q:

postgreSQL Reversing REGEXP_SPLIT TO TABLE

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?

A:

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.

