CODEWITHSUNDEEP
mysqlsql
mohan111
mohan111

Reputation: 8865

How to transpose rows to columns based on time intervals in MYSQL

I have sample data like this : 

            ID Val   Name        Dt                 Status
            1, 145, 'Test1', '2020-01-28 02:18:00', 'open'
            2, 145, 'Test2', '2020-01-28 04:10:00', 'open'
            3, 145, 'Test3', '2020-01-28 05:50:00', 'open'
            4, 145, 'Test3', '2020-01-28 05:56:00', 'close'
            5, 145, 'Test4', '2020-01-28 07:36:00', 'open'
            6, 145, 'Test4', '2020-01-28 07:42:00', 'open'
            7, 145, 'Test4', '2020-01-28 07:44:00', 'open'
            8, 145, 'Test4', '2020-01-28 07:47:00', 'close'

How can i get the output like this :

        ID Val   Name        o_Dt                 o_gate      c_Dt             c_gate
        1, 145, 'Test1', '2020-01-28 02:18:00', 'open'        NULL               NULL
        2, 145, 'Test2', '2020-01-28 04:10:00', 'open'        NULL               NULL
        3, 145, 'Test3', '2020-01-28 05:50:00', 'open'  '2020-01-28 05:56:00', 'close'
        4, 145, 'Test4', '2020-01-28 07:36:00', 'open'  '2020-01-28 07:47:00', 'close'

I Have tried with different scenarios but not moving forward Using

COALESCE(LAG(Status) OVER (ORDER BY dt) 

ROW_NUMBER()OVER(PARTITION BY vehicle_id,status )

Not getting exact result . Can anyone suggest on this .

Previously I have asked question for same data set but now requirement got changed .

Link : How to fetch First open and close status in mysql

Upvotes: 0

Views: 110

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270463

One method uses lag():

select t.*
from (select t.*,
             lag(status) over (partition by val, name order by date) as prev_status
      from t
     ) t
where status = 'open' and
      (prev_status is null or prev_status <> 'open');

This can return more than one result for a test, if the status can "return" to 'open'. You can use row_number() if you don't want this behavior:

select t.*
from (select t.*,
             row_number() over (partition by val, name, status order by date) as seqnum
      from t
     ) t
where status = 'open' and seqnum = 1;

EDIT:

(for adjusted data)

You can just use conditional aggregation:

select val, name,
       min(case when status = 'open' then status end) as o_gate,
       min(case when status = 'open' then dt end) as o_dt,
       max(case when status = 'close' then status end) as c_gate,
       max(case when status = 'close' then dt end) as c_dt,
from t
group by val, name;

Here is a db<>fiddle

If you want to reconstruct the id, you can use an expression like:

row_number() over (order by min(dt)) as id

Upvotes: 1

Related Questions