时间戳

首页 > 解决方案 > SQL Server 中的多列数据透视

tsql - SQL Server 中的多列数据透视

问题描述

我有3个领域：

Melt, HSM, LSM

在某些地区产生的每个订单都有以下数据：

Start Date, Finish Date, Weight

我有一个Viewin SQL Server 2012（顶部图像），如何使用 tsql 创建一个生成底部图像的枢轴？ 在此处输入图像描述

标签： tsqlsql-server-2012pivot

解决方案

您可以使用条件聚合：

SELECT [Order],
    MAX(CASE WHEN Area = 'Melt' THEN StartDate   END) AS Melt_SDate,
    MAX(CASE WHEN Area = 'Melt' THEN FinisthDate END) AS Melt_FDate,
    MAX(CASE WHEN Area = 'Melt' THEN Weight      END) AS Melt_Weight,

    MAX(CASE WHEN Area = 'HSM' THEN StartDate    END) AS HSM_SDate,
    MAX(CASE WHEN Area = 'HSM' THEN FinisthDa    END) AS HSM_FDate,
    MAX(CASE WHEN Area = 'HSM' THEN Weight       END) AS HSM_Weight,

    MAX(CASE WHEN Area = 'LSM' THEN StartDate    END) AS LSM_SDate,
    MAX(CASE WHEN Area = 'LSM' THEN FinisthDate  END) AS LSM_FDate,
    MAX(CASE WHEN Area = 'LSM' THEN Weight       END) AS LSM_Weight
FROM tab_name
GROUP BY [Order];   -- ORDER is reserved word, you should avoid such identifiers;

为了使其更简洁，您可以使用IIF：

MAX(CASE WHEN Area = 'Melt' THEN StartDate   END) AS Melt_SDate,
<=>
MAX(IIF(Area='Melt',StartDate,NULL)) AS Melt_SDate,

推荐阅读