0

I have a attempted pivot table that follows:

 SELECT * FROM 
(SELECT 
      [SurveyID]
      ,[SurveyQuestion]
      ,[SurveyQuestionID]
      ,[SequenceNumber]      
  FROM [Succession_Tool].[dbo].[SurveyQuestions]
) X
PIVOT
(
    SUM([SurveyID]) FOR [SurveyQuestion] IN (A,B,C)
) Z

I get the following error:

Msg 325, Level 15, State 1, Line 9
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 tried to rewrite it based on some other articles on stackoverflow and other places such as this or this but they both lead back to the same error above. Is there something simple I'm missing? For what its worth, my compatibility_level is 100, this is SQL Serer 2008, I don't need the aggregation because just want the formatting to spit out to a CSV file

UPDATE: I rewrote the query again below following these directions

 WITH MYSurveyQuestions AS 
(SELECT 
      [SurveyID]
      ,[SurveyQuestion]
      ,[SurveyQuestionID]
      ,[SequenceNumber]      
  FROM [Succession_Tool].[dbo].[SurveyQuestions]
) 
SELECT * FROM MYSurveyQuestions
PIVOT   
(SUM(SURVEYID) FOR SurveyQuestion
    IN (A,B,C,D,E,F)) AS PVT

Now I get the following error: 

Msg 102, Level 15, State 1, Line 11 Incorrect syntax near ')'.

Unfortunately, that has the same meaning as the first error to me because I still can't find the error(shrug)...I feel like I'm closer though. Double clicking the error highlights

(SUM(SURVEYID) FOR SurveyQuestion

Community
  • 1
  • 1
Jason R.
  • 379
  • 1
  • 6
  • 19
  • What result do you get when you run `SELECT @@VERSION`? – GarethD Dec 16 '14 at 17:28
  • Microsoft SQL Server 2008 R2 (SP2) - 10.50.4000.0 (X64) Jun 28 2012 08:36:30 Copyright (c) Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 6.1 (Build 7601: Service Pack 1) (Hypervisor) @GarethD – Jason R. Dec 16 '14 at 17:58
  • Both the current database and the one you are selecting from should have the correct compatibility level. – Andriy M Dec 16 '14 at 18:34
  • 1
    Actually I was mistaken, only the current database must have the required compatibility level. The other one(s) (e.g. `Succession_Tool` in your example) doesn't have to. So, just as the message said, it was probably the current database, whatever it was, that needed fixing. – Andriy M Dec 16 '14 at 18:46
  • All data is coming from the same database(Succession_Tool), which has a level of 100 – Jason R. Dec 16 '14 at 18:55
  • Don't you need square brackets (or single quotes) around the letters in parentheses? [A],[B] etc. – influent Dec 16 '14 at 19:47
  • Same error with square brackets (single quotes throws a error), Thanks @influent – Jason R. Dec 16 '14 at 20:12
  • Shot in the dark but did you try "select * from sys.databases" to see all the compatibility levels according to that view? – influent Dec 16 '14 at 20:58
  • @influent, yes that particular database I'm hitting is 100 and that is the only source I hit. Thanks – Jason R. Dec 16 '14 at 21:19
  • Thanks, just making sure Mgmt Studio wasn't lying. – influent Dec 16 '14 at 21:21
  • Never mind where the data were coming from, was `Succession_Tool` the *current* database when you were running the query? If not, what compatibility level did the database that was current have? – Andriy M Dec 17 '14 at 10:00
  • The succession_Tool is the only and current database the data is coming from and the compatibility level is 100, Thanks @AndriyM – Jason R. Dec 17 '14 at 13:05
  • The reason why it didn't work is because what @AndriyM and "influent" suggested earlier, it was a compatibility_level problem, but all my fault still. I was unknowningly connected to another database that had a Level of 80, but didn't realize it because I had a fully qualified database name. Succession_Tool has a Level of 100. One of you should post this as a answer so you can get credit for it. My apologies for such a simple problem, thank you all for your help. – Jason R. Dec 17 '14 at 15:06

0 Answers0