替代Dynamic PIVOT的单查询方案求解——项目资源分配表场景

嘿，我完全懂你被动态PIVOT卡得难受的感觉——要处理可变列又想只用单查询搞定，确实得绕点弯子。先帮你补全下Allocation表的完整创建语句（看起来你之前没写完），毕竟要基于完整的表结构来给出方案：

CREATE TABLE Allocation (
    [AllocationID] INT IDENTITY PRIMARY KEY,
    [ProjectID] INT FOREIGN KEY REFERENCES Projects(ProjectID),
    [ResourceID] INT FOREIGN KEY REFERENCES Resources(ResourceID),
    [AllocationDate] DATE,
    [HoursAllocated] DECIMAL(5,2) -- 假设存储的是每日分配工时
);

下面给你三种可行的单查询替代方案，分别适配不同的需求场景：

方案1：静态条件聚合（资源列表相对稳定时首选） #

如果你的资源列表不会频繁变动，直接用条件聚合就能替代动态PIVOT，完全是单查询，性能还比动态SQL好：

SELECT
    p.ProjectID,
    p.Name AS ProjectName,
    a.AllocationDate,
    SUM(CASE WHEN r.Name = 'ResourceA' THEN a.HoursAllocated END) AS [ResourceA],
    SUM(CASE WHEN r.Name = 'ResourceB' THEN a.HoursAllocated END) AS [ResourceB],
    SUM(CASE WHEN r.Name = 'ResourceC' THEN a.HoursAllocated END) AS [ResourceC]
    -- 按实际资源名称继续添加CASE语句
FROM Allocation a
JOIN Projects p ON a.ProjectID = p.ProjectID
JOIN Resources r ON a.ResourceID = r.ResourceID
GROUP BY p.ProjectID, p.Name, a.AllocationDate;

方案2：动态列的单查询实现（资源动态变化时） #

如果资源是动态新增/删除的，不想每次改SQL，那可以用STRING_AGG结合sp_executesql把动态逻辑打包成单查询，不用单独声明变量：

EXEC sp_executesql N'
WITH ResourceColumns AS (
    SELECT 
        STRING_AGG(QUOTENAME(Name), '', '') AS ColumnList,
        STRING_AGG(''SUM(CASE WHEN r.Name = '' + QUOTENAME(Name, '''''') + '' THEN a.HoursAllocated END) AS '' + QUOTENAME(Name), '', '') AS AggregationColumns
    FROM Resources
)
SELECT
    p.ProjectID,
    p.Name AS ProjectName,
    a.AllocationDate,
    '' + AggregationColumns + ''
FROM Allocation a
JOIN Projects p ON a.ProjectID = p.ProjectID
JOIN Resources r ON a.ResourceID = r.ResourceID
CROSS JOIN ResourceColumns
GROUP BY p.ProjectID, p.Name, a.AllocationDate, AggregationColumns;
';

这个查询会自动读取当前所有资源，动态生成对应的列和聚合逻辑，本质还是动态SQL但被包装成了单查询执行。

方案3：XML格式输出（无需动态列时） #

如果你的应用可以处理XML格式的结果，那这种方案最简洁，完全不用动态逻辑：

SELECT
    p.ProjectID,
    p.Name AS ProjectName,
    a.AllocationDate,
    (
        SELECT 
            r.Name AS ResourceName,
            a2.HoursAllocated
        FROM Allocation a2
        JOIN Resources r ON a2.ResourceID = r.ResourceID
        WHERE a2.ProjectID = p.ProjectID AND a2.AllocationDate = a.AllocationDate
        FOR XML PATH('Resource'), ROOT('Allocations'), TYPE
    ) AS ResourceAllocations
FROM Projects p
JOIN Allocation a ON p.ProjectID = a.ProjectID
GROUP BY p.ProjectID, p.Name, a.AllocationDate;

返回的结果里，每个项目-日期对应的资源分配会以XML节点的形式存在，应用层可以轻松解析。

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