透视基于表中的两列[重复]

Question

我有一张这样的桌子

PCode      Milestone      DeliveryDate       Status
------------------------------------------------------
P1234        Start             14/5/2019     Complete
P1234        End               17/6/2019     Complete
P2345        Start              8/6/2019      Progress
P2345        End                19/6/2019     Progress
P7335        Start              18/8/2019     Provisional
P7335        End                19/9/2019     Provisional
-----------------------------------------------------------

基于以上数据，我需要得到这样一个表

PCode     Start          End        Status
---------------------------------------------
P1234     14/05/2019    17/06/2019   Completed
P2345     8/6/2019      19/06/2019   Progress
P7335     18/08/2019   19/09/2019    Provisional
----------------------------------------------

我不知道如何使用一些PIVOT来实现这一点

Answer 1

一个简单的条件聚合应该可以做到

这假设整个PCode记录中的状态一致

Selet PCode
     ,[Start]  = min(case when Milestone='Start' then DeliveryDate end)
     ,[Ebd]    = max(case when Milestone='End'   then DeliveryDate end)
     ,[Status] = max([Status])
 From  YourTable
 Group By PCode

编辑-查找开始和结束状态

Select PCode
     ,[Start]       = min(case when Milestone='Start' then DeliveryDate end)
     ,[End]         = max(case when Milestone='End'   then DeliveryDate end)
     ,[StatusStart] = min(case when Milestone='Start' then [Status] end)
     ,[StatusEnd]   = min(case when Milestone='End'   then [Status] end)
 From   YourTable
 Group By PCode