Pivot Based on Two columns from a table

Question

I've a table like this

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
-----------------------------------------------------------

Based on the above data I need to get a table like this

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
----------------------------------------------

Im not having an idea how to achieve this using some PIVOT

For a given pair of records, is it ever possible that the `Status` file might not be in agreement, and, if so, what is the logic for choosing which of the two values to report? — Tim Biegeleisen, Sep 02 '19 at 14:33
Looks like conditional aggregation to me. What have *you* tried so far to solve the issue? — Thom A, Sep 02 '19 at 14:35
@TimBiegeleisen THis is not converting into rows since we are not making columns with different start and end date. its creating a column against all rows with start and end values. — Sandeep Thomas, Sep 02 '19 at 14:38

John Cappelletti · Accepted Answer · 2019-09-02T15:46:12.953

2

A simple conditional aggregation should do the trick

This assumes [Status] is consistent across PCode records

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

EDIT - To Find Start and End Status

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

edited Sep 02 '19 at 15:46

answered Sep 02 '19 at 14:43

John Cappelletti

79,615
7
44
66

@SandeepThomas Always happy to help – John Cappelletti Sep 02 '19 at 14:51
If it answers the question, @SandeepThomas, please consider marking it as the solution. – Thom A Sep 02 '19 at 14:57
@JohnCappelletti Sorry a question.. If status is not same for a pcode? what we can do?? – Sandeep Thomas Sep 02 '19 at 15:38
@SandeepThomas One moment. We can get the last status by date – John Cappelletti Sep 02 '19 at 15:40
@JohnCappelletti I need to find what is the start and end status.. :( – Sandeep Thomas Sep 02 '19 at 15:41
1

@SandeepThomas See updated answer – John Cappelletti Sep 02 '19 at 15:46

Pivot Based on Two columns from a table

1 Answers1