rloza rloza - 4 months ago 9
SQL Question

SQL Server - Many to Many query into 1 row

I have 3 tables

Movies:


  • Movie_id

  • Title

  • Runtime

  • Date_id

  • ...



Genre:


  • Genre_id

  • Genre



Movie_Genre:


  • Movie_id

  • Genre_id



I want to put them in one row so that each genre is separated by a column
like so:

Movie_id | Title | Genre1 | Genre2 | Genre3 | Genre4 |

Each movie may or may not have 4 genres.

I came close with this approach:

select Movies.Movie_id,
Max(Movies.Title),
MAX(CASE WHEN p.RowNum=1 THEN p.Genre_id END) as Genre1,
MAX(CASE WHEN p.RowNum=2 THEN p.Genre_id END) as Genre2,
MAX(CASE WHEN p.RowNum=3 THEN p.Genre_id END) as Genre3,
MAX(CASE WHEN p.RowNum=4 THEN p.Genre_id END) as Genre4


FROM Movies
JOIN
(
SELECT Movie_Genre.*,
ROW_NUMBER() OVER (PARTITION BY Movie_id ORDER BY Genre_id)
as RowNum

FROM Movie_Genre
) as P
ON Movies.Movie_id=p.Movie_id
GROUP BY Movies.movie_id


But I end up with the Genre_id instead of the Genre name.
Also the title column is not named

Answer

INNER JOIN to the Genre table in the sub query

select  Movies.Movie_id,
        Movies.Title,
        MAX(CASE WHEN p.RowNum=1 THEN p.Genre END) as Genre1,
        MAX(CASE WHEN p.RowNum=2 THEN p.Genre END) as Genre2,
        MAX(CASE WHEN p.RowNum=3 THEN p.Genre END) as Genre3,
        MAX(CASE WHEN p.RowNum=4 THEN p.Genre END) as Genre4
FROM    Movies
    INNER JOIN
    (
        SELECT  mg.Movie_id, g.Genre,
                ROW_NUMBER() OVER (PARTITION BY mg.Movie_id ORDER BY mg.Genre_id) 
                as RowNum
        FROM    Movie_Genre mg
                INNER JOIN Genre g  on  mg.Genre_id = g.Genre_id
    ) as P  ON Movies.Movie_id=p.Movie_id
GROUP BY Movies.movie_id, Movies.Title