SQL Server 2005错误-“"MAX""错误不是公认的表格提示选项" [英] SQL Server 2005 Error - ""MAX" is not a recognized table hints option"

问题描述

我正在尝试使用动态SQL对需要动态生成列名的表进行数据透视. 我的代码是:

I am trying to use dynamic SQL to do a pivot on a table for which I need to dynamically generate the column names. My code is:

DECLARE @columns varchar(max)
DECLARE @query varchar(max)
SELECT @columns = COALESCE(@columns + ',[' + cast([Name] as varchar(max)) + ']', 
                 '[' + cast([Name] as varchar(max))+ ']')           
    FROM   dbo.Temp2

SET @query = 'SELECT * FROM dbo.Temp2 AS PivotData'
SET @query = @query  + 
'PIVOT (MAX(VALUE) FOR [NAME] IN (' + @columns + ')) AS p'                              

EXEC (@query)

我的@columns函数似乎可以正常工作(尽管我只能打印" 8000个字符以进行验证)，并且我读到在SQL 2005中对非数字varchar进行MAX或MIN函数是可以接受的，但是当我以完整的形式运行查询，但收到错误消息:

My @columns function seems to work (though I can only 'print' 8000 characters to verify), and I have read that it is acceptable to do a MAX or MIN function on non-numeric varchars in SQL 2005, but when I run the query in its complete form I get the error message:

Msg 321, Level 15, State 1, Line 1
"MAX" is not a recognized table hints option. If it is intended as a parameter to a table-valued function, ensure that your database compatibility mode is set to 90.

我已经检查了兼容性级别，并将其设置为90. 谁能提出任何建议以解决这个问题?

I have checked the compatibility level and it is set to 90. Can anyone offer any suggestion for how to get past this?

非常感谢.

推荐答案

您缺少PivotData和PIVOT之间的空格.

    SET @query = @query  + 
    ' PIVOT (MAX(VALUE) FOR [NAME] IN (' + @columns + ')) AS p' 
//   ^--- HERE

结果，SQL解析器将PivotDataPIVOT解释为单个标识符，此后会导致语法错误.

As the result, the SQL parser interprets PivotDataPIVOT as a single identifier, resulting in a syntax error later on.

这篇关于SQL Server 2005错误-“"MAX""错误不是公认的表格提示选项"的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！