Sorting Values into Different Columns

I have a table like this:

OrderID   OrderStatus     DateCreated
--------------------------------------------
13     3             2018-10-25 09:33:14.937
13     4             2018-10-25 09:33:19.010
13     7             2018-10-25 18:03:37.137
14     3             2018-10-25 09:43:45.363
14     4             2018-10-25 09:43:50.690
14     7             2018-10-25 09:54:55.150
15     2             2018-10-25 14:04:06.380
16     3             2018-10-25 14:31:53.060
16     4             2018-10-25 14:32:00.040
16     7             2018-10-25 14:39:10.160
17     3             2018-10-25 15:52:20.750
17     4             2018-10-25 15:52:35.937
17     7             2018-10-25 18:03:47.130
18     3             2018-10-25 15:58:47.923
18     4             2018-10-25 15:59:00.420
18     7             2018-10-25 18:03:47.160

There are 8 statuses. I want a query which returns the order id and the dateCreated for each status. If a status has been skipped, I want there to be a null value.

So, for example:

OrderId    status1    status2  status3  ....   status8
-------------------------------------------------------
 12          time        null     time   ....    time

I have tried using multiple joins, but the fact that most of the times statuses have been skipped has proven to be a problem.

select 
    o1.OrderId,
    o1.DateCreated as 'AwaitingExternalPayment', 
    o2.DateCreated as 'PaymentSettledButNotYetSuppliedToStore', 
    o3.DateCreated as 'ReceivedByStore', 
    o4.DateCreated as 'InOven',
    o5.DateCreated as 'ReadyForDispatch',
    o6.DateCreated as 'OutForDelivery',
    o7.DateCreated as 'Completed',
    o8.DateCreated as 'Canceled'
from 
    OrderStatusHistory as o1
left join 
    OrderStatusHistory as o2 on o1.OrderId = o2.OrderId
                             and case
                                    when o2.OrderStatusEnum = 2 then 1
                                    else 0
                                 end = 1
left join 
    OrderStatusHistory as o3 on o2.OrderId = o3.OrderId
                             and case
                                    when o3.OrderStatusEnum = 3 then 1
                                    else 0
                                 end = 1
-- repeat this "left join" for every status

Answer

You can use conditional aggregation:

select osh.OrderID,
       min(case when OrderStatus = 1 then DateCreated end) as status_1,
       min(case when OrderStatus = 2 then DateCreated end) as status_2,
       min(case when OrderStatus = 3 then DateCreated end) as status_3,
       min(case when OrderStatus = 4 then DateCreated end) as status_4,
       min(case when OrderStatus = 5 then DateCreated end) as status_5,
       min(case when OrderStatus = 6 then DateCreated end) as status_6,
       min(case when OrderStatus = 7 then DateCreated end) as status_7,
       min(case when OrderStatus = 8 then DateCreated end) as status_8
from OrderStatusHistory osh
group by osh.OrderID;

Advertisement

Answer