如何在SQL Server中基于单个字段对三列执行PIVOT操作？

你可以用条件聚合替代多次JOIN的方式，只扫描一次原表就能完成多列透视，大幅提升查询效率，同时逻辑更简洁。

假设你的原表包含EmployeeID、LocationStatus、Location、StartDate、EndDate字段，以下是实现代码：

SELECT
    EmployeeID,
    -- 透视Location列
    MAX(CASE WHEN LocationStatus = 'Primary' THEN Location END) AS Primary_Location,
    MAX(CASE WHEN LocationStatus = 'Secondary' THEN Location END) AS Secondary_Location,
    MAX(CASE WHEN LocationStatus = 'Tertiary' THEN Location END) AS Tertiary_Location,
    -- 透视StartDate列
    MAX(CASE WHEN LocationStatus = 'Primary' THEN StartDate END) AS Primary_StartDate,
    MAX(CASE WHEN LocationStatus = 'Secondary' THEN StartDate END) AS Secondary_StartDate,
    MAX(CASE WHEN LocationStatus = 'Tertiary' THEN StartDate END) AS Tertiary_StartDate,
    -- 透视EndDate列
    MAX(CASE WHEN LocationStatus = 'Primary' THEN EndDate END) AS Primary_EndDate,
    MAX(CASE WHEN LocationStatus = 'Secondary' THEN EndDate END) AS Secondary_EndDate,
    MAX(CASE WHEN LocationStatus = 'Tertiary' THEN EndDate END) AS Tertiary_EndDate
FROM
    YourOriginalTable
GROUP BY
    EmployeeID;

方案优势 #

1. 性能提升：仅需扫描一次原表，避免了多次JOIN带来的重复表扫描和连接开销，尤其是数据量较大时差异明显。
2. 逻辑简洁：所有透视逻辑在单查询内完成，无需维护多个子查询和连接条件，降低后续维护成本。
3. 稳定性高：静态条件聚合在已知LocationStatus取值的场景下，执行计划更稳定，不会出现动态SQL可能的性能波动。

如果LocationStatus的取值是动态不确定的，可以基于当前表的状态值生成动态SQL，但静态条件聚合仍是已知取值场景下的最优选择。

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