Incorrect syntax near 'PIVOT'

Question

I'm running SQL Server 2008 R2. I'm trying to build a table that takes data from a table structured like this:

company | ded_id | descr

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

And I need to build a temp table it out to 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

So running this gives the following error:

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.

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?

Answer 1

The possible reason for that type of issue is you imported database from other source which might be running older version of SQL Server. Anyway, you have way to get out of it. Follow steps below:

1. Right click on Database (e.g. Northwind).
2. Click 'Properties'.
3. Click 'Options', from left pane, under 'Select a page' section.
4. Select appropriate database version as per your installation from drop-down of 'Compatibility level' at right.
5. Save changes and try now.

Below is screenshot of Properties window for your reference.

Answer 2

I had the same error (Incorrect syntax near 'PIVOT'...) and solved it by changing compatibility level in the Options section of database properties.

I also found how to script the compatibility level change:

ALTER DATABASE database_name   
SET COMPATIBILITY_LEVEL = 100

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