sql-server - 动态数据透视表语法错误

Question

我收到错误消息-
我运行以下查询时的Msg 102, Level 15, State 1, Line 5 Incorrect syntax near ','.。我不明白为什么语法不正确。有没有人有什么建议？

DECLARE @Vendor AS INT = 41

CREATE TABLE #PivotData
(
    [ID] INT,
[Row] VARCHAR(MAX),
[Columns] VARCHAR(MAX),
[AggData] INT
)

INSERT INTO #PivotData

SELECT  V.Vendor_Key
    ,O.Location_Desc 
    ,P.Item_Desc
    ,IIF.Sales_Qty
FROM    PDI.PDI_Warehouse_952_01.dbo.Item_Inventory_Fact IIF
    INNER JOIN  PDI.PDI_Warehouse_952_01.dbo.Item_Purchases_Fact IPF
    ON IIF.Calendar_Key = IPF.Calendar_Key
    AND IIF.Organization_Key = IPF.Organization_Key
    AND IIF.Product_Key = IPF.Product_Key
INNER JOIN  PDI.PDI_Warehouse_952_01.dbo.Organization O
    ON IIF.Organization_Key = O.Organization_Key
INNER JOIN  PDI.PDI_Warehouse_952_01.dbo.Product P
    ON IIF.Product_Key = P.Product_Key
INNER JOIN PDI.PDI_Warehouse_952_01.dbo.Vendor V
    ON IPF.Vendor_Key = V.Vendor_Key
WHERE V.Vendor_key = @Vendor

DECLARE @Cols AS NVARCHAR(MAX),
    @Query AS NVARCHAR(MAX)

SET @Cols = STUFF ((SELECT DISTINCT ',' + QUOTENAME(P.Columns)
                FROM #PivotData P
                FOR XML PATH(''), TYPE
                ).value('.', 'NVARCHAR(MAX)')
                ,1,1,'')

 SET @Query = 'SELECT Row, ' + @Cols + ' FROM
            (
                SELECT  Row
                        ,Column
                        ,Aggregate
                FROM #PivotData
            )x
            PIVOT
            (
                SUM(Aggregate)
                FOR Row IN (' + @Cols + ')
            ) p '

EXECUTE(@Query)

DROP TABLE #PivotData

Answer 1

该错误消息不是很有用，但是如果您查看生成的查询及其中的第五行，则会看到该错误发生，因为您尝试使用不正确的名称从列中进行选择。

用列名[Row], [Columns], [AggData]创建临时表

但是，当您选择并从中旋转时，您将使用以下列名称Row, Column, Aggregate
那不会很好。解决方案是在@query中使用正确的列名。