Incorrect syntax next to 'PIVOT'

Question

I am running SQL Server 2008 R2. I am trying to build a table that takes data from a table structured as follows:

company | ded_id | descr

10          1      MEDINS
10          2      LIFE
10          3      PENSN
...
10         50      DOMREL

And I need to create a tempo table in a format like this:

company | DESC1 | DESC2 | DESC3 ... | DESC50
10        MEDINS  LIFE    PENSN       DOMREL

So, I built the following query:

    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

Thus, this leads to the following error:

Msg 325, Level 15, State 1, Line 6 Invalid syntax next to "PIVOT". You may need to set the compatibility level of the current database to a higher value to enable this feature. See the Help for the SET COMPATIBILITY_LEVEL option for ALTER DATABASE.

I double checked the compatibility level in the database and it is already set to 100, so this may not be a problem. Can you come up with any other settings that might cause this behavior?

Answer 1

, SQL Server. , . :

• (, Northwind).
• "".
• "", , " ".
• " " .
• .

"" .

Answer 2

( "PIVOT"...) , .

, script :

ALTER DATABASE database_name   
SET COMPATIBILITY_LEVEL = 100

(. https://msdn.microsoft.com/en-us/library/bb510680.aspx?f=255&MSPPError=-2147217396)