SQL Server如何将聚合查询与无ActionId的PIVOT透视结果关联

问题原因 #

SQL Server的PIVOT运算符执行时，会自动将源查询中除聚合列、FOR子句指定列之外的列作为分组依据，同时FOR子句指定的列会被转换为新的列名，原列会被移除。
你原有PIVOT语句中FOR子句指定的是ActionId，所以该列被转换为[1]、[2]等透视列名，自然不会出现在结果中。

解决方案 #

你需要关联的成本统计结果是按ActionId分组的，说明你实际需要的是每个ActionId对应一行、不同Field作为列存储Value的透视结果，只需将FOR子句的指定列改为Field即可，调整后的PIVOT查询如下：

SELECT 
    ActionId,
    [Group],
    [1] AS Field1_Value,
    [2] AS Field2_Value,
    [3] AS Field3_Value,
    [4] AS Field4_Value,
    [5] AS Field5_Value,
    [6] AS Field6_Value
FROM (
    SELECT [ActionId], [Group], [Field], [Value] 
    FROM ActionData
) src
PIVOT (
    MAX([Value]) 
    FOR [Field] IN ([1],[2],[3],[4],[5],[6])
) ppp

完整关联查询 #

调整后的PIVOT结果保留了ActionId列，可以直接和你的成本统计查询关联，完整代码如下：

WITH CostAgg AS (
    SELECT
        ai.Id AS ActionId,
        ai.Name,
        SUM(c.Cost) AS TotalCost
    FROM ActionItem ai
    LEFT JOIN Costing c ON ai.Id = c.ActionId 
    GROUP BY ai.Id, ai.Name
),
PivotData AS (
    SELECT 
        ActionId,
        [Group],
        [1] AS Field1_Value,
        [2] AS Field2_Value,
        [3] AS Field3_Value,
        [4] AS Field4_Value,
        [5] AS Field5_Value,
        [6] AS Field6_Value
    FROM (
        SELECT [ActionId], [Group], [Field], [Value] 
        FROM ActionData
    ) src
    PIVOT (
        MAX([Value]) 
        FOR [Field] IN ([1],[2],[3],[4],[5],[6])
    ) ppp
)
SELECT *
FROM CostAgg ca
LEFT JOIN PivotData pd ON ca.ActionId = pd.ActionId

运行上述查询即可同时获得每个动作项的总成本、以及对应的所有属性明细的行转列结果。

内容的提问来源于stack exchange，提问作者jamheadart