Incorrect syntax near '1'. Expecting ID, QUOTED_ID, or '.' Error with SQL Pivot

Question

I have a table with stats for universities that looks like this:

StatID | UniversityID | StatValue
1      | 1            | 100
2      | 1            | 90
3      | 1            | 80
1      | 2            | 50
2      | 2            | 55

I'd like a query to return something like this:

(Rows are StatIDs, Columns are UniversityIDs)
StatID | 1             | 2             | 3
1      | 100           | 50            | NULL
2      | 90            | 55            | NULL
3      | 80            | NULL          | NULL

Here's my query:

SELECT StatID, 1, 2, 3
FROM 
    (SELECT StatID, UniversityID, StatValue FROM @table) up
PIVOT 
    (MAX(StatValue) FOR UniversityID IN (1, 2, 3)) AS pvt
ORDER BY StatisticID;

I get an error on FOR UniversityID IN (1, saying:

Incorrect syntax near '1'. Expecting ID, QUOTED_ID, or '.'.

What am I doing wrong? Does it have something to do with an int as a column header?

I will be using this with ~260,000 rows (~300 columns and ~3,000 rows)

Vice

What is the query that you have tried? – Incalculable

Answer 1

You have the synatx for the IN wrong:

SELECT StatisticID, 1, 2, 3
FROM
     (SELECT StatisticID, UniversityID, Value
     FROM @table) up
PIVOT
     (MAX(Value) FOR UniversityID IN ([1], [2], [3])) AS pvt
ORDER BY StatisticID;

Incautious

Do you really want the MAX aggregator here. I would think you want SUM although maybe it doesn't matter what aggregator you choose if you only have one value as in that case SUM and MAX will be the same (and so will MIN) – Incalculable

Answer 2

Given what you want to produce as output, I am not sure you need to use the PIVOT operator. You can get pretty close to the output you have above with the following query:

SELECT s.StatID
       ,UniversityID1 = SUM(CASE WHEN UniversityID = 1 THEN StatValue ELSE NULL END)
       ,UniversityID2 = SUM(CASE WHEN UniversityID = 2 THEN StatValue ELSE NULL END)
       ,UniversityID3 = SUM(CASE WHEN UniversityID = 3 THEN StatValue ELSE NULL END)
  FROM StatsTable s
 GROUP BY s.StatID

which will produce

StatID | UniversityID1 | UniversityID2 | UniversityID3  
1      | 100           | 50            | NULL
2      | 90            | 55            | NULL
3      | 80            | NULL          | NULL

It doesn't have the last row with StatID = 4, but I am not sure what value that is providing to you anyway as all the values are uniformly NULL and there is no StatID = 4 data in your input table.

If you really want the PIVOT syntax, here it is:

SELECT StatID
       ,UniversityID1 = [1]
       ,UniversityID2 = [2]
       ,UniversityID3 = [3]
  FROM 
      (SELECT StatID, UniversityID, StatValue FROM @table) up
 PIVOT 
      (SUM(StatValue) FOR UniversityID IN ([1], [2], [3])) AS pvt
 ORDER BY StatID;

(You were missing your square brackets [])

Incalculable

sorry, I just modified the question with my best attempt – Vice

Answer 3

In my case, I was getting this error because I had a table named "User" which is also a keyword in SQL Server. Adding square brackets around ("User" -> "[User]") solved the issue. Please refer to the image below for more clarity:

Chinkapin