T-SQL 透视错误：'0'附近的语法不正确

Question

我不确定我在代码中做错了什么。我收到一条错误消息：

Msg 102，级别 15，状态 1，第 82
行 "0"附近的语法不正确

有人可以帮助找到我的错误是什么吗？我正在尝试对表进行透视，以便查询返回带有年龄组而不是行的标题。

SELECT *
FROM 
(SELECT
ID,
CASE 
WHEN Age BETWEEN 0 AND 4 THEN '0-4 Years'
WHEN  Age  BETWEEN 5 AND 24 THEN '5-24 Years' 
WHEN Age BETWEEN 25 AND 49 THEN '25-49 Years'
WHEN Age BETWEEN 50 AND 64 THEN '50-64 Years'
WHEN Age > 64 THEN '> 64 Years'
END AS GroupAge
FROM 
#AD) t
PIVOT
(COUNT(ID)
FOR GroupAge IN 
(0-4 Years,5-24 Years,25-49 Years,> 64 Years)
) AS pvt

Answer 1

在透视列名称两边加上方括号 []。

PIVOT
(
COUNT(ID)
FOR GroupAge IN 
([0-4 Years],[5-24 Years],[25-49 Years],[50-64 Years],[> 64 Years])
) AS pvt

Answer 2

该错误导致您需要在数据透视列中使用方括号，因为0-4 Years.. 列不是普通字符串。

我会使用条件聚合函数做透视。

SELECT
COUNT(CASE WHEN Age BETWEEN 0 AND 4 THEN  ID END) '0-4 Years', 
COUNT(CASE WHEN Age BETWEEN 5 AND 24  THEN  ID END) '5-24 Years' , 
COUNT(CASE WHEN Age BETWEEN 25 AND 49 THEN  ID END) '25-49 Years', 
COUNT(CASE WHEN Age BETWEEN 50 AND 64 THEN  ID END) '50-64 Years', 
COUNT(CASE WHEN  Age > 64 THEN  ID END) '> 64 Years'
FROM #AD