SQL : Geeting Order's status timestamp in a nested query

Question

I'm working on a Order Table which has all the details regarding the order's that were allocated. The sample DB example is

Order ID   Order Status     Action Date
23424      ALC              1571467792094280
23424      PIK              1571467792999990
23424      PAK              1571469792999990
23424      SHP              1579967792999990
33755      ALC              1581467792238640
33755      PIK              1581467792238640
33755      PAK              1581467792238640
33755      SHP              1581467792238640

In the table I have order ID , status, action_date (the action dates updated when ever there is an update on order status against the update timestamp, the action_date is unix time)

I'm trying to write a query that can provide me the Order ID, ALC_AT, PIK_AT, PAK_AT, SHP_AT Basically all the timestamp updates against a Order ID within one row, I know it can be done via Nested Query but, I'm unable to figure how how to do it.

Any help would be highly appreciated.

Edit (As asked to provide the sample result ) :

Order ID   Order Status     ALC_AT             PIK_AT            PAK_AT              SHP_AT
23424      SHP              1571467792094280   1571467792999990  1571469792999990    1579967792999990

I could use that but that can't be used as , if i want to pull out the data of the order's that were shipped yesterday my condition will be "WHERE order_status = 'SHP' and from_unixtime(action_date / 100000) = Current_date -1" which will only give the shipped timestamp details . I want to capture all the updates that happened against the shipped orders. — r_a, Oct 21 '19 at 06:39
Then it will be kind of you if you add sample result set what you need. — James, Oct 21 '19 at 06:49
Search for mysql pivot and mysql conditional aggregation and mysql rows to columns. — P.Salmon, Oct 21 '19 at 06:53
Possible duplicate of [MySQL - Rows to Columns](https://stackoverflow.com/questions/1241178/mysql-rows-to-columns) — P.Salmon, Oct 21 '19 at 06:54
@James I have added a sample result. I hope it give you an idea on how i want the result to be. — r_a, Oct 21 '19 at 06:57

Arif Sher Khan · Accepted Answer · 2019-10-21T08:08:54.143

1

I am not sure how it is done in mysql. But below describes how it will be done in Oracle. You can searh more for PIVOT in mysql to help you in the same.

select * 
  from (select order_id,
               status, 
               action_date 
          from order)  
 pivot (max(status) 
        for app_id in ( 'ALC' as 'ALC_AT', 'PIK' as 'PIK_AT', 'PAK' as 'PAK_AT', 'SHP' as 'SHP_AT'))

Hope this will help you.

EDIT for mysql:

select * 
  from (select "order.order_number",
               "shipment.status", 
               from_unixtime("action_date"/1000000) as "action_date"
          from order_table 
         where "order.order_number" = '2019-10-19-N2-6411')
  pivot (max("action_date")
         for "shipment_status" in ( 'ALC' AS 'ALC_AT', 'PIK' AS 'PIK_AT', 'PAK' 
                                     AS 'PAK_AT', 'SHP' AS 'SHP_AT'))

edited Oct 21 '19 at 08:08

answered Oct 21 '19 at 06:58

Arif Sher Khan

461
4
12

1

please note that this won't help the OP. – James Oct 21 '19 at 07:11
Thanks. I think this logic and pivot function my help me get the desired result. – r_a Oct 21 '19 at 07:31
@Arif Sher Khan Hi I have written the query as below : select * from (select "order.order_number", "shipment.status", from_unixtime("action_date"/1000000) from order_table) pivot (max("action_date") for "shipment_status" in ( 'ALC' AS 'ALC_AT', 'PIK' AS 'PIK_AT', 'PAK' AS 'PAK_AT', 'SHP' AS 'SHP_AT')) AND "order.order_number" = '2019-10-19-N2-6411'; But it is showing error : Execution operation: line 6:12: mismatched input '(' expecting {',', ')'} It is showing error on the pivot line.any idea what could be wrong ? – r_a Oct 21 '19 at 07:45
1

Hey Anshul. Thanks. I have added the code for you in answer(EDIT for mysql heading). See if that helps. – Arif Sher Khan Oct 21 '19 at 08:12

SQL : Geeting Order's status timestamp in a nested query

1 Answers1

Linked