sql-server - 如何在 SQL Server 中旋转日期表？

Question

我有一个containerID有很多日期的号码。我使用下面的代码将每个日期转换为一列。问题是每一列都有一组三个空值。我正在尝试将具有相同容器 ID 的四行合并为一行，其中四列上有日期。

示例数据：

    ContainerID MovementMilestone   MilestoneDate
    ---------------------------------------------
    ACCU4035970 Left Yard           2021-02-01
    ACCU4035970 Empty Return        2021-02-01
    ACCU4035970 Yard Arrival        2021-01-29
    ACCU4035970 Gate Out            2021-01-29

代码：

SELECT
    ContainerID,
    CASE WHEN MovementMilestone = 'Gate Out' THEN MilestoneDate END AS 'mm_GateOut',
    CASE WHEN MovementMilestone = 'Yard Arrival' THEN MilestoneDate END AS ' mm_YardArrival',
    CASE WHEN MovementMilestone = 'Left Yard' THEN MilestoneDate END AS 'mm_LeftYard',
    CASE WHEN MovementMilestone = 'Empty Return' THEN MilestoneDate END AS 'mm_EmptyReturn'
FROM 
    #movementmilestone

输出：

   ContainerID  mm_GateOut   mm_YardArrival mm_LeftYard mm_EmptyReturn
   ------------------------------------------------------------------------
   ACCU4035970  NULL         NULL           2021-02-01  NULL
   ACCU4035970  NULL         NULL           NULL        2021-02-01
   ACCU4035970  NULL         2021-01-29     NULL        NULL
   ACCU4035970  2021-01-29   NULL           NULL        NULL

我知道 SQL Server 上的 PIVOT 函数，但以下来自 Internet 的示例使我明白了这一点：

SELECT *
FROM
    (SELECT
         containerID,
         MovementMilestone,
         MilestoneDate
     FROM 
         #movementmilestone) AS #source
PIVOT 
    (MAX(MilestoneDate) FOR [MovementMilestone]
     IN ([GateOut], [YardArrival], [LeftYard], [EmptyReturn]))
    AS #alias

输出：

   ContainerID  GateOut YardArrival LeftYard    EmptyReturn
   ACCU4035970  NULL    NULL        NULL        NULL

谢谢你的帮助！

标签： sql-serverpivotpivot-table

Answer 1

--OLD FASHIONED WAY <GRIN>

CREATE TABLE #movementmilestone(
   ContainerID       VARCHAR(30) NOT NULL
  ,MovementMilestone VARCHAR(11) NOT NULL
  ,MilestoneDate     DATE  NOT NULL
 );
INSERT INTO #movementmilestone(ContainerID,MovementMilestone,MilestoneDate) VALUES ('ACCU4035970','Left','2021-02-01');
INSERT INTO #movementmilestone(ContainerID,MovementMilestone,MilestoneDate) VALUES ('ACCU4035970','Empty','2021-02-01');
INSERT INTO #movementmilestone(ContainerID,MovementMilestone,MilestoneDate) VALUES ('ACCU4035970','Yard','2021-01-29');
INSERT INTO #movementmilestone(ContainerID,MovementMilestone,MilestoneDate) VALUES ('ACCU4035970','Gate','2021-01-29');


SELECT
       ContainerID,
       MAX( CASE WHEN MovementMilestone = 'Gate' THEN MilestoneDate END ) AS 'mm_GateOut',
       MAX (CASE WHEN MovementMilestone = 'Yard' THEN MilestoneDate END ) AS ' mm_YardArrival',
       MAX( CASE WHEN MovementMilestone = 'Left' THEN MilestoneDate END ) AS 'mm_LeftYard',
       MAX ( CASE WHEN MovementMilestone = 'Empty' THEN MilestoneDate END ) AS 'mm_EmptyReturn'
    FROM #movementmilestone
    GROUP BY ContainerID