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?

Wearproof

I can't reproduce this error with my SQL Server 2008 R2 SP1 (10.50.2500) server; the query works fine and produce the desired output. – Glynas

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.

Etty

I am on SQL SERVER 2012 , didn't see ''Select a page' section in OPtions – Acquaintance

In the screenshot above, there is 'Options' in the list at left. – Etty

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)

Orts

Answer 3

i faced the same problem... while using "Pivot" and "Format" and "Convert" command inside sql query.

one thing that worked for me was instead of importing sqlite i imported pyodbc so my code changed

from : import sqlite3 conn = sqlite3.connect('/Users/****/Desktop/****.sqlite')

to: import pyodbc conn = pyodbc.connect('Driver={SQL Server};''Server=@@@@;''Database=****;' 'Trusted_Connection=yes;')

Woodcutter

Answer 4

I know it's not the same thing that was asked, but it solved it for me and it can help other people. pivot and a SQL reserved word, so it presents the error, what solved it was to put it in square brackets.

select * from ledgers where [pivot]

Buchholz

It looks like the question was related to the PIVOT-function itself, so escaping it wouldn't really help. – Mandi