Loop Through a Table to concatenate Rows

Question

I have a table of similar structure:

Name Movies_Watched
A    Terminator
B    Alien
A    Batman
B    Rambo
B    Die Hard

....

I am trying to get this:

Name  Movies_Watched
A     Terminator;Batman
B     Alien, Die Hard, Rambo

My initial guess was:

SELECT Name, Movies_Watched || Movies_Watched from TABLE

But obviously that's wrong. Can someone tell me how can I loop through the 2nd column and concatenate them? What's the logic like?

Got to know that group_concat is the right approach. But haven't been able to figure it out yet. When I've tried:

select name, group_concat(movies_watched) from table group by 1

But it throws an error saying User-defined transform function group_concat must have an over clause

Answer 1

As already mentioned, in Vertica it's LISTAGG():

WITH
input(nm,movies_watched) AS (
          SELECT 'A','Terminator'
UNION ALL SELECT 'B','Alien'
UNION ALL SELECT 'A','Batman'
UNION ALL SELECT 'B','Rambo'
UNION ALL SELECT 'B','Die Hard'
)
SELECT
  nm AS "Name"
, LISTAGG(movies_watched) AS movies_watched
FROM input
GROUP BY nm;
-- out  Name |    movies_watched    
-- out ------+----------------------
-- out  A    | Terminator,Batman
-- out  B    | Alien,Rambo,Die Hard
-- out (2 rows)
-- out 
-- out Time: First fetch (2 rows): 12.735 ms. All rows formatted: 12.776 ms