IMDb Ratings: Pivot/Unpivot

Question

I'm working on a personal project where I'm looking at IMDb movie ratings across a group of friends. I am trying to create a fact table that has each movie and the ratings for each rater for each movie (NULL if no rating).

Here is what I'm currently dealing with:

There are 7 raters: Alex, James, Walker, Mitchell, Robbie G., Robbie A., and Jacob. Correct me if this form is wrong, but I'd like to have 7 columns, one for each rater. This way Movie IDs would be distinct. I'm trying to visualize this in Power BI and I need Movie IDs to be distinct.

Another issue is with the DateCreated attribute. How would I deal with this? Add 7 more columns for each MovieID?

Here's what I currently have that isn't working:

SELECT [MovieID]
      ,[Rater]
      ,[UserRating]
      ,[DateCreated]
  FROM [IMDbRatings].[dbo].[vetl_fUserRatings]
  PIVOT([UserRating] FOR [Rater] IN(Mitchell,Walker,James,Alex,Jacob,Robbie A.,Robbie G.)) as x

score 1 · Answer 1 · answered Aug 24 '15 at 18:50

SELECT 
   [MovieID]
  ,max(case when [Rater] = 'Mitchell' then [UserRating] end) as "Mitchell"
  ,max(case when [Rater] = 'Walker' then [UserRating] end) as "Walker"
  ,max(case when [Rater] = 'James' then [UserRating] end) as "James"     
  -- likewise for other users
  ,max([DateCreated])
FROM [IMDbRatings].[dbo].[vetl_fUserRatings]
group by [movieid]

You can do it with case statements if you know the number of users is fixed. Else, you will have to use dynamic sql.

IMDb Ratings: Pivot/Unpivot

1 Answers1