SQL Server基于日期的动态Pivot Table实现问题咨询

核心思路 #

你的需求需要组合使用UNPIVOT（逆透视）和动态PIVOT（透视）实现：

1. 先通过逆透视把原表中Labor、Equipment两个数值列转为行维度，生成成本类型列
2. 再通过动态拼接SQL生成对应Project Id的月份列，不需要提前写死静态月份值。由于同一个Project Id+月份+成本类型的组合在原表中只有唯一一条记录，使用MAX/MIN聚合不会改变原始值，不会出现你担心的聚合异常问题。

完整实现代码 #

通用动态版本（适配所有Project Id，无需手动改列名） #

-- 替换为你要查询的Project Id
DECLARE @TargetProjectId INT = 1;
DECLARE @MonthColumns NVARCHAR(MAX), @DynamicSQL NVARCHAR(MAX);

-- 动态获取目标Project对应的所有月份，拼接为透视列
SELECT @MonthColumns = STRING_AGG(QUOTENAME([Projected Month]), ', ')
FROM 你的实际表名
WHERE [Project Id] = @TargetProjectId
GROUP BY [Projected Month]
ORDER BY [Projected Month];

-- 拼接完整执行SQL
SET @DynamicSQL = N'
SELECT *
FROM (
    -- 逆透视：把Labor、Equipment转为行
    SELECT 
        CostType = Type,
        [Projected Month],
        Amount = Value
    FROM 你的实际表名
    UNPIVOT (
        Value FOR Type IN (Labor, Equipment)
    ) AS UnpivotResult
    WHERE [Project Id] = @TargetProjectId
) AS SourceData
PIVOT (
    MAX(Amount)
    FOR [Projected Month] IN (' + @MonthColumns + N')
) AS PivotResult';

-- 执行SQL
EXEC sp_executesql @DynamicSQL, N'@TargetProjectId INT', @TargetProjectId = @TargetProjectId;

兼容低版本SQL Server（2016及以下，无STRING_AGG函数） #

如果你的数据库版本不支持STRING_AGG，把拼接列的部分替换为以下代码即可：

SELECT @MonthColumns = STUFF((
    SELECT DISTINCT ',' + QUOTENAME([Projected Month])
    FROM 你的实际表名
    WHERE [Project Id] = @TargetProjectId
    ORDER BY ',' + QUOTENAME([Projected Month])
    FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)'), 1, 1, '');

临时静态查询版本（仅用于固定Project Id的快速查询） #

如果只是临时查某一个Project的结果，也可以直接写死月份列，不需要动态SQL：

-- 查询Project Id=1的示例
SELECT *
FROM (
    SELECT 
        CostType = Type,
        [Projected Month],
        Amount = Value
    FROM 你的实际表名
    UNPIVOT (
        Value FOR Type IN (Labor, Equipment)
    ) AS UnpivotResult
    WHERE [Project Id] = 1
) AS SourceData
PIVOT (
    MAX(Amount)
    FOR [Projected Month] IN ([2021-09-01], [2021-10-01], [2021-11-01])
) AS PivotResult;

注意事项 #

把所有代码中的你的实际表名替换为你自己的表名即可正常运行，查询不同Project仅需修改@TargetProjectId变量的值即可，输出结果完全符合你给出的预期格式。

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