How to make PIVOT a table / Cross tab query?
<p>I have made a <code>SELECT</code> statement to get this output:</p>

<pre><code>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
</code></pre>

<p>Here is the <code>SELECT</code> statement:</p>

<pre><code>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;
</code></pre>

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

<pre><code>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
</code></pre>

<p>My attempt to do this:</p>

<pre><code>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)
)
</code></pre>

<p>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.</p>

<p>EDIT:</p>

<p>Here are the tables associated:</p>

<p><a href="http://pastebin.com/yCmYPtAC" rel="nofollow noreferrer">File1 (Pastebin)</a></p>

<p><a href="http://pastebin.com/etWUTC63" rel="nofollow noreferrer">File2 (Pastebin)</a></p>