主页 / dba / 问题 / 71750
Accepted
Andrew
Asked: 2014-07-17 16:06:38 +0800 CST

为什么 UNPIVOT 在兼容级别 80 数据库上工作？

  • 772

我正在尝试在以兼容级别 80 运行的 SQL Server 2008 SP3 数据库上使用 UNPIVOT 提取数据。这应该意味着 UNPIVOT 不起作用，但在我的情况下，它在某些情况下会起作用......

作品：

形式的独立SELECT查询：

SELECT...FROM...UNPIVOT...WHERE...GROUP BY

不起作用：

相同的查询，在LEFT JOIN ()同一服务器内不同数据库上的其他表上。都在兼容级别 80。

我收到通常的错误消息：

Msg 325, Level 15, State 1, Line 165
Incorrect syntax near 'UNPIVOT'. 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.

UNPIVOT变通办法似乎很麻烦，如果可能的话，我希望这个查询是独立的和可刷新的。如果我可以让查询单独工作，这似乎是合乎逻辑的，它应该可以在JOIN.

问题：

为什么这有时会起作用？

在这些条件下如何实现UNPIVOT里面的子查询？JOIN

Unpivot 子查询：（根据请求...）

SELECT 
    YEAR(CAST('1-'+UnPiv.[Date] AS DATE)) "Year",
    MONTH(CAST('1-'+UnPiv.[Date] AS DATE)) "Month",
    CASE [Channel_Org]
        WHEN 'TPR' THEN 'ERP'
        ELSE [Channel_Org]
    END AS [Channel_Org],
    ISNULL(SUM(CASE WHEN [GL Desc] = 'EIP' AND 
        [OPEX_Group] = 'Postpaid SAC' THEN [Amount] END),0) "Post EIP",
    ISNULL(SUM(CASE WHEN [GL Desc] = 'EIP' AND
        [OPEX_Group] = 'Prepaid SAC' THEN [Amount] END),0) "Pre EIP",
    ISNULL(SUM(CASE WHEN [GL Desc] = 'EIP' AND
        [OPEX_Group] = 'SRC' THEN [Amount] END),0) "Upg EIP",
    ISNULL(SUM(CASE WHEN [GL Desc] = 'MDF (OEM)' AND
        [OPEX_Group] = 'Postpaid SAC' THEN [Amount] END),0) "Post MDF",
    ISNULL(SUM(CASE WHEN [GL Desc] = 'MDF (OEM)' AND
        [OPEX_Group] = 'Prepaid SAC' THEN [Amount] END),0) "Pre MDF",
    ISNULL(SUM(CASE WHEN [GL Desc] = 'MDF (OEM)' AND
        [OPEX_Group] = 'SRC' THEN [Amount] END),0) "Upg MDF"
FROM [Channel_Steering].[dbo].[AQ1_OPEX_View]
UNPIVOT (Amount FOR [Date] IN ( [Jan-14],
                                [Feb-14],
                                [Mar-14],
                                [Apr-14],
                                [May-14],
                                [Jun-14],
                                [Jul-14],
                                [Aug-14],
                                [Sep-14],
                                [Oct-14],
                                [Nov-14],
                                [Dec-14])) UnPiv
WHERE   (   [Channel_Org] IN ('Retail','TPR')
                AND
            [GL Desc] IN ('MDF (OEM)', 'EIP')
        )
GROUP BY
    [Channel_Org],
    YEAR(CAST('1-'+UnPiv.[Date] AS DATE)),
    MONTH(CAST('1-'+UnPiv.[Date] AS DATE))
sql-server unpivot

1 个回答

  1. Best Answer

    答案在错误消息中：

    您可能需要将当前数据库的兼容级别设置为更高的值才能启用此功能。

    其中“当前数据库”是指上下文数据库——即执行查询的数据库，它不一定与查询中引用的数据库相同。例如，以下会产生错误消息：

    USE AdventureWorks;
GO
ALTER DATABASE AdventureWorks 
SET COMPATIBILITY_LEVEL = 80;
GO
SELECT
    U.SystemInformationID,
    U.[Database Version],
    U.ColumnName,
    U.Value
FROM AdventureWorks.dbo.AWBuildVersion AS ABV
UNPIVOT 
(
    Value 
    FOR ColumnName IN 
        (
        VersionDate,
        ModifiedDate
        )
) AS U;

    以下成功执行（假设tempdb兼容级别 > 80）：

    USE AdventureWorks;
GO
ALTER DATABASE AdventureWorks 
SET COMPATIBILITY_LEVEL = 80;
GO
USE tempdb; -- Context database
GO
SELECT
    U.SystemInformationID,
    U.[Database Version],
    U.ColumnName,
    U.Value
FROM AdventureWorks.dbo.AWBuildVersion AS ABV -- Still references data in AW DB
UNPIVOT 
(
    Value 
    FOR ColumnName IN 
        (
        VersionDate,
        ModifiedDate
        )
) AS U;

    您需要检查产生错误的连接的上下文数据库是什么。

    • 10

相关问题