SQL Collect duplicates to one place? PostgreSQL

Sorry I’m new here and I’m also new with SQL and can’t really explain my problem in the title…

So I have a TV show database, and there I have a Genre column, but for a TV show there are multiple Genres stored, so when I’m selecting all my TV Shows how can I combine them?

It needs to look like this:

https://i.stack.imgur.com/3EhBj.png

So I have to combine the string together, here is my code so far what I wrote:

SELECT title,
               year,
               runtime,
               MIN(name) as name,
               ROUND(rating, 1) as rating,
               trailer,
               homepage
        FROM shows
        JOIN show_genres 
            on shows.id = show_genres.show_id 
        JOIN genres 
            on show_genres.genre_id = genres.id
        GROUP BY title,
               year,
               runtime,
               rating,
               trailer,
               homepage
        ORDER BY rating DESC
        LIMIT 15;

I also have some other stuff here, that’s my exerciese tasks! Thanks!

Also here is the relationship model:

https://i.stack.imgur.com/M89ho.png

Answer

Basically you need string aggregation – in Postgres, you can use string_agg() for this.

For efficiency, I would recommend moving the aggregation to a correlated subquery or a lateral join rather than aggregating in the outer query, so:

SELECT 
    s.title,
    s.year,
    s.runtime,
    g.genre_names,
    ROUND(s.rating, 1) as rating,
    s.trailer,
    s.homepage
FROM shows s
LEFT JOIN LATERAL (
    SELECT string_agg(g.name, ', ') genre_names
    FROM show_genres sg
    INNER JOIN genres g ON g.id = sg.genre_id
    WHERE sg.show_id = s.id
) g ON 1 = 1
ORDER BY s.rating DESC
LIMIT 15