CODEWITHSUNDEEP
sqlt-sqlsql-server-2008-r2
JohnnyMnemonic
JohnnyMnemonic

Reputation: 61

Incorrect syntax near 'PIVOT'

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?

Upvotes: 6

Views: 23905

Answers (4)

Alexandre Santos Gomes
Alexandre Santos Gomes

Reputation: 11

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]

Upvotes: 0

harshil marwah
harshil marwah

Reputation: 1

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;')

Upvotes: 0

Sanja Ž
Sanja Ž

Reputation: 11

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)

Upvotes: 1

Altaf Patel
Altaf Patel

Reputation: 1410

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.

enter image description here

Upvotes: 14

Related Questions