'1' 附近的语法不正确。需要 ID、QUOTED_ID 或“.” SQL Pivot 错误
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9338400/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Incorrect syntax near '1'. Expecting ID, QUOTED_ID, or '.' Error with SQL Pivot
提问by Greg
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:
我得到一个错误FOR UniversityID IN (1,说：
Incorrect syntax near '1'. Expecting ID, QUOTED_ID, or '.'.
What am I doing wrong? Does it have something to do with an intas a column header?
我究竟做错了什么？它与int作为列标题有关吗？
I will be using this with ~260,000 rows (~300 columns and ~3,000 rows)
我将在 ~260,000 行（~300 列和 ~3,000 行）中使用它
回答by jmoreno
You have the synatx for the IN wrong:
你有 IN 错误的语法：
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;
回答by Steven Schroeder
Given what you want to produce as output, I am not sure you need to use the PIVOToperator.
You can get pretty closeto the output you have above with the following query:
考虑到您想要作为输出生成的内容，我不确定您是否需要使用PIVOT运算符。您可以使用以下查询非常接近上面的输出：
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.
它没有 StatID = 4 的最后一行，但我不确定它提供给您的值是什么，因为所有值都统一为 NULL 并且您的输入表中没有 StatID = 4 数据。
If you really want the PIVOTsyntax, here it is:
如果你真的想要PIVOT语法，这里是：
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 [])
（您缺少方括号 []）
