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)

Solution 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;

Solution 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 [])