SQL Server 2005错误—“MAX”不是一个可识别的表提示选项。

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:

我尝试使用动态SQL在一个表上做一个主元，我需要动态地生成列名。我的代码是:

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)

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:

我@ column功能似乎工作(虽然我只能打印8000字符验证),和我读过,它是可以接受的最大值或最小值函数在SQL 2005的非数字varchar格式,但当我运行查询的完整形式得到错误信息:

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.

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

我检查了兼容性级别，设置为90。有谁能给我们提些建议，让我们如何度过这个难关吗?

Many thanks in advance.

提前感谢。

1 个解决方案

#1

You are missing a space between PivotData and PIVOT.

在数据透视数据和主元之间缺少一个空间。

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

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

因此，SQL解析器将PivotDataPIVOT解释为一个单一的标识符，之后会导致语法错误。

#1