title: Incorrect syntax near 'PIVOT'
body: <p>I'm running SQL Server 2008 R2.  I'm trying to build a table that takes data from a table structured like this:</p>
  
  <pre><code>company | ded_id | descr
  
  10          1      MEDINS
  10          2      LIFE
  10          3      PENSN
  ...
  10         50      DOMREL
  </code></pre>
  
  <p>And I need to build a temp table it out to a format like this:</p>
  
  <pre><code>company | DESC1 | DESC2 | DESC3 ... | DESC50
  10        MEDINS  LIFE    PENSN       DOMREL
  </code></pre>
  
  <p>So I built the following query:</p>
  
  <pre class="lang-sql prettyprint-override"><code>    SELECT *
      FROM (
      SELECT company,'DESC'+CAST(ded_id as VARCHAR(2)) AS DedID,descr
      FROM deduction     
      ) deds
      PIVOT (MAX(descr)FOR DedID IN([DESC1],[DESC2],[DESC3])) descs
  </code></pre>
  
  <p>So running this gives the following error:</p>
  
  <blockquote>
    <p>Msg 325, Level 15, State 1, Line 6
    Incorrect syntax near 'PIVOT'. You may need to set the compatibility level of the current database to a higher value to enable this feature. See help for the SET COMPATIBILITY_LEVEL option of ALTER DATABASE.</p>
  </blockquote>
  
  <p>I double checked the compatibility level on the database and it is already set to 100 so that can't be the issue.  Can you think of any other setting that might be causing this behavior?</p>