SQL Server 2012中使用T-SQL创建多列透视表的方法

嘿，针对你在SQL Server 2012里基于生产视图生成透视表的需求，我给你整理了两种实用的T-SQL实现方法，你可以根据实际场景选择：

方法1：使用PIVOT运算符（简洁高效） #

SQL Server 2005及以上版本支持PIVOT运算符，适合快速实现固定列的透视。假设你的视图名为v_ProductionOrders，且包含唯一标识订单的OrderID字段（如果没有，替换成你实际的分组键即可），代码示例如下：

SELECT
    OrderID,
    -- 转换回原始数据类型
    CAST(Melt_StartDate AS DATE) AS Melt_StartDate,
    CAST(HSM_StartDate AS DATE) AS HSM_StartDate,
    CAST(LSM_StartDate AS DATE) AS LSM_StartDate,
    CAST(Melt_FinishDate AS DATE) AS Melt_FinishDate,
    CAST(HSM_FinishDate AS DATE) AS HSM_FinishDate,
    CAST(LSM_FinishDate AS DATE) AS LSM_FinishDate,
    CAST(Melt_Weight AS DECIMAL(18,2)) AS Melt_Weight,
    CAST(HSM_Weight AS DECIMAL(18,2)) AS HSM_Weight,
    CAST(LSM_Weight AS DECIMAL(18,2)) AS LSM_Weight
FROM (
    -- 先将多字段数据统一为键值对格式，为透视做准备
    SELECT
        OrderID,
        CONCAT(ProductionArea, '_StartDate') AS PivotCol,
        CAST(StartDate AS VARCHAR(20)) AS PivotVal
    FROM v_ProductionOrders
    UNION ALL
    SELECT
        OrderID,
        CONCAT(ProductionArea, '_FinishDate') AS PivotCol,
        CAST(FinishDate AS VARCHAR(20)) AS PivotVal
    FROM v_ProductionOrders
    UNION ALL
    SELECT
        OrderID,
        CONCAT(ProductionArea, '_Weight') AS PivotCol,
        CAST(Weight AS VARCHAR(20)) AS PivotVal
    FROM v_ProductionOrders
) AS SourceData
PIVOT (
    MAX(PivotVal)
    FOR PivotCol IN (
        Melt_StartDate, HSM_StartDate, LSM_StartDate,
        Melt_FinishDate, HSM_FinishDate, LSM_FinishDate,
        Melt_Weight, HSM_Weight, LSM_Weight
    )
) AS PivotResult
ORDER BY OrderID;

小说明： #

• 用UNION ALL把三个业务字段和生产区域拼接成统一的透视列，让PIVOT能一次性处理所有字段。
• 因为PIVOT必须配合聚合函数，日期字段用MAX()（每个订单+区域的日期唯一，MAX和MIN结果一致），重量字段用MAX()或SUM()都可以，若同一订单在同一区域有多条重量记录，SUM()会自动求和。
• 最后把透视后的字符串类型转换回原始的日期/数值类型，保证数据格式正确。

方法2：使用CASE WHEN手动透视（直观易调试） #

如果觉得PIVOT的嵌套结构不好理解，用CASE WHEN手动生成透视列会更清晰，适合需要自定义调整的场景：

SELECT
    OrderID,
    -- 透视各区域的开始日期
    MAX(CASE WHEN ProductionArea = 'Melt' THEN StartDate END) AS Melt_StartDate,
    MAX(CASE WHEN ProductionArea = 'HSM' THEN StartDate END) AS HSM_StartDate,
    MAX(CASE WHEN ProductionArea = 'LSM' THEN StartDate END) AS LSM_StartDate,
    -- 透视各区域的结束日期
    MAX(CASE WHEN ProductionArea = 'Melt' THEN FinishDate END) AS Melt_FinishDate,
    MAX(CASE WHEN ProductionArea = 'HSM' THEN FinishDate END) AS HSM_FinishDate,
    MAX(CASE WHEN ProductionArea = 'LSM' THEN FinishDate END) AS LSM_FinishDate,
    -- 透视各区域的重量
    SUM(CASE WHEN ProductionArea = 'Melt' THEN Weight END) AS Melt_Weight,
    SUM(CASE WHEN ProductionArea = 'HSM' THEN Weight END) AS HSM_Weight,
    SUM(CASE WHEN ProductionArea = 'LSM' THEN Weight END) AS LSM_Weight
FROM v_ProductionOrders
GROUP BY OrderID
ORDER BY OrderID;

小说明： #

• 对每个生产区域的每个字段，用CASE WHEN筛选出对应的值，再通过聚合函数将同一订单的多行数据合并为一行。
• 这种方法不需要转换数据类型，逻辑一目了然，后续要添加新生产区域或调整字段时，直接修改CASE WHEN条件即可。

注意点： #

1. 记得把代码里的v_ProductionOrders替换成你实际的视图名称。
2. 如果视图里没有OrderID，换成你实际的订单唯一标识字段（比如OrderNumber），确保分组后能正确聚合每个订单的数据。
3. 若Weight是整数类型，把DECIMAL(18,2)改成INT就行，匹配你的实际数据类型。

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