“PIVOT"附近的语法不正确 [英] Incorrect syntax near 'PIVOT'

问题描述

我正在运行 SQL Server 2008 R2.我正在尝试构建一个表，该表从如下结构的表中获取数据:

I'm running SQL Server 2008 R2. I'm trying to build a table that takes data from a table structured like this:

company | ded_id | descr

10          1      MEDINS
10          2      LIFE
10          3      PENSN
...
10         50      DOMREL

我需要建立一个临时表，格式如下:

And I need to build a temp table it out to a format like this:

company | DESC1 | DESC2 | DESC3 ... | DESC50
10        MEDINS  LIFE    PENSN       DOMREL

所以我构建了以下查询:

So I built the following query:

    SELECT *
    FROM (
    SELECT company,'DESC'+CAST(ded_id as VARCHAR(2)) AS DedID,descr
    FROM deduction     
    ) deds
    PIVOT (MAX(descr)FOR DedID IN([DESC1],[DESC2],[DESC3])) descs

所以运行这个会出现以下错误:

So running this gives the following error:

消息 325，级别 15，状态 1，第 6 行'PIVOT' 附近的语法不正确.您可能需要将当前数据库的兼容级别设置为更高的值才能启用此功能.请参阅有关 ALTER DATABASE 的 SET COMPATIBILITY_LEVEL 选项的帮助.

Msg 325, Level 15, State 1, Line 6 Incorrect syntax near 'PIVOT'. You may need to set the compatibility level of the current database to a higher value to enable this feature. See help for the SET COMPATIBILITY_LEVEL option of ALTER DATABASE.

我仔细检查了数据库的兼容性级别，它已经​​设置为 100，所以这不是问题.您能想到可能导致这种行为的任何其他设置吗?

I double checked the compatibility level on the database and it is already set to 100 so that can't be the issue. Can you think of any other setting that might be causing this behavior?

推荐答案

出现此类问题的可能原因是您从其他可能运行旧版 SQL Server 的来源导入了数据库.无论如何，你有办法摆脱它.请按照以下步骤操作:

The possible reason for that type of issue is you imported database from other source which might be running older version of SQL Server. Anyway, you have way to get out of it. Follow steps below:

1. 右键单击数据库(例如 Northwind).
2. 点击属性".
3. 点击左侧窗格中选择页面"部分下的选项".
4. 从右侧的兼容性级别"下拉菜单中根据您的安装选择合适的数据库版本.
5. 保存更改并立即尝试.

以下是属性窗口的截图供您参考.

Below is screenshot of Properties window for your reference.

这篇关于“PIVOT"附近的语法不正确的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！