Unpivoting movie genres

Question

I'm looking at some movie data from IMDb and I have found a limitation in my knowledge. Here is the data I'm looking at:

I think I will run into problems with this current table. How can I get it so that there are two columns, MovieID and Genre? It would then cause MovieID to not be distinct. Would this require unpivoting?

Any help would be much appreciated!

Easiest option would be to only store those with the genre (rather than film 'x' isn't a 'comedy') So, you *could* do something like `SELECT MoveId, 'Action' WHERE Action = 1 UNION ALL SELECT MoveId, 'Crime' WHERE Crime = 1 UNION ALL -- etc.` — Rowland Shaw, Aug 24 '15 at 20:18
It sounds like you want to actually properly normalize this. Kudos for that. And yes, this table structure will cause you pain in the long run. The best way to deal with this is to split this out into three tables. One for the Movie information. A second table of Genres. And a third table with a composite key of MovieID and GenreID. — Sean Lange, Aug 24 '15 at 20:33

score 1 · Accepted Answer · answered Aug 24 '15 at 20:19

I can't see your screenshot so I'm guessing at the columns, but something along the lines of:

SELECT MovieID, "Horror" As Genre
WHERE Horror = 1
UNION ALL
SELECT MovieID, "Comedy" As Genre
WHERE Comedy = 1
UNION ALL
SELECT MovieID, "Drama" As Genre
WHERE Drama = 1
UNION ALL
...

score 0 · Answer 2 · answered Aug 24 '15 at 20:33

I do think UNPIVOT would give you the kind of thing you're after. Something like this should work (I've purposely simplified the example to only include three genres, but you should be able to get the idea from what I've done):-

SELECT MovieID, Genre, [Count]
FROM 
    (SELECT MovieID, Crime, Comedy, Drama --comma separated list of the genres
    FROM MovieGenres) p
UNPIVOT
    ([Count] FOR Genre IN 
        (Crime, Comedy, Drama) --list genres again
    ) as upvt

SQLFIDDLE

If you only want to show the genres/movieIDs where the value is 1 then it's a simple case of adding a WHERE clause to the above:-

SELECT MovieID, Genre, [Count]
FROM 
    (SELECT MovieID, Crime, Comedy, Drama --list your genres here
    FROM #MovieGenres) p
UNPIVOT
    ([Count] FOR Genre IN 
        (Crime, Comedy, Drama) --list genres again, same order
    ) as upvt
WHERE [Count] = 1

Unpivoting movie genres

2 Answers2