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)
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;
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With