Asad Mahmood Asad Mahmood - 5 days ago 5
SQL Question

How to make PIVOT a table / Cross tab query?

I have made a

SELECT
statement to get this output:

MOVIE Critic Type Average Rating
------------------------ ------------ --------------
30 Minutes or Less Critic 2.00
30 Minutes or Less User 4.20
A Lonely Place to Die Critic 10.00
A Lonely Place to Die User 8.50
Taken Critic 6.17
Taken User 7.27
Taken 2 Critic 4.00
Taken 2 User 6.29

8 rows selected


Here is the
SELECT
statement:

SELECT MovieTitle AS "MOVIE", TBLCRITICCLASS.CRITICCLASSDESC AS "Critic Type", AVG(TBLREVIEW.REVIEWSTAR) AS "Average Rating"
FROM TBLMOVIE
INNER JOIN TBLREVIEW ON TBLMOVIE.MOVIEID = TBLREVIEW.MOVIEID
INNER JOIN TBLCRITIC ON TBLREVIEW.CRITICID = TBLCRITIC.CRITICID
INNER JOIN TBLCRITICCLASS ON TBLCRITIC.CRITICCLASSID = TBLCRITICCLASS.CRITICCLASSID
GROUP BY MovieTitle, TBLCRITICCLASS.CRITICCLASSDESC
ORDER BY Movietitle;


I want to turn this table into this output with a
PIVOT
to get the output with the critic rating and user rating as it's own category:

MOVIE Critic Rating User Rating
------------------------ ------------- -----------
30 Minutes or Less 2.00 4.20
A Lonely Place to Die 10.00 8.50
Taken 6.17 7.27
Taken 2 4.00 6.29


My attempt to do this:

SELECT * FROM
(
SELECT MovieTitle AS "MOVIE", AVG(TBLREVIEW.REVIEWSTAR) AS "Critic Rating", AVG(TBLREVIEW.REVIEWSTAR) AS "User Rating"
FROM TBLMOVIE
INNER JOIN TBLREVIEW ON TBLMOVIE.MOVIEID = TBLREVIEW.MOVIEID
INNER JOIN TBLCRITIC ON TBLREVIEW.CRITICID = TBLCRITIC.CRITICID
INNER JOIN TBLCRITICCLASS ON TBLCRITIC.CRITICCLASSID = TBLCRITICCLASS.CRITICCLASSID
GROUP BY MovieTitle, TBLCRITICCLASS.CRITICCLASSDESC
ORDER BY Movietitle
)

PIVOT
(
AVG(TBLREVIEW.REVIEWSTAR) AS "Critic Rating"
FOR TBLREVIEW.REVIEWSTAR IN (TBLCRITICCLASS.CRITICCLASSDESC)

AVG(TBLREVIEW.REVIEWSTAR) AS "User Rating"
FOR TBLREVIEW.REVIEWSTAR IN (TBLCRITICCLASS.CRITICCLASSDESC)
)


I get errors, It's probably due to my lack of skill with subqueries, I would like to understand why I am not getting my preferred output and how to fix it.

EDIT:

Here are the tables associated:

File1 (Pastebin)

File2 (Pastebin)

Answer

You just have your pivot statement wrong. You could substitute your query for the CTE below and it should work.

SELECT * 
FROM (SELECT MovieTitle AS "MOVIE"
           , TBLCRITICCLASS.CRITICCLASSDESC AS      "Critic Type"
           , AVG(TBLREVIEW.REVIEWSTAR) AS "Average Rating"
      FROM TBLMOVIE 
      INNER JOIN TBLREVIEW 
        ON TBLMOVIE.MOVIEID = TBLREVIEW.MOVIEID 
      INNER JOIN TBLCRITIC 
        ON TBLREVIEW.CRITICID = TBLCRITIC.CRITICID 
      INNER JOIN TBLCRITICCLASS 
        ON TBLCRITIC.CRITICCLASSID = TBLCRITICCLASS.CRITICCLASSID 
      GROUP BY MovieTitle, TBLCRITICCLASS.CRITICCLASSDESC
      ORDER BY Movietitle;
      )
PIVOT (AVG("Average Rating") for "Critic Type" 
   in ('Critic' as "Critic Rating", 'User' as "User Rating")))
ORDER BY Movie

As to, "Why I am not getting my preferred output"

You are piviting on "Critic Type" for which you have two values ('critic' and user') the aggregrate avg makes sense; but in your example you only have 1 value for each record so min/max would work as well.

In a pivot you specify each column in the "in" portion of the pivot, letting the DB engine determine how to orgainze the data based on the values in the "Critic Type" column.

Because you have to specify each value, you can't have a dynamic range, without dynamic SQL.

Note: you can alias the columns in the "IN" portion of the pivot if you desire different names than the values in the "for" pivot field.

enter image description here

Comments