By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table t as
select 1 as id, 145 as val, 'Test1' as name, cast('2020-01-28 02:18:00' as datetime) as dt, 'open' as status union all
select 2 as id, 145 as val, 'Test2' as name, cast('2020-01-28 04:10:00' as datetime) as dt, 'open' as status union all
select 3 as id, 145 as val, 'Test3' as name, cast('2020-01-28 05:50:00' as datetime) as dt, 'open' as status union all
select 4 as id, 145 as val, 'Test3' as name, cast('2020-01-28 05:56:00' as datetime) as dt, 'close' as status union all
select 5 as id, 145 as val, 'Test4' as name, cast('2020-01-28 07:36:00' as datetime) as dt, 'open' as status union all
select 6 as id, 145 as val, 'Test4' as name, cast('2020-01-28 07:42:00' as datetime) as dt, 'open' as status union all
select 7 as id, 145 as val, 'Test4' as name, cast('2020-01-28 07:44:00' as datetime) as dt, 'open' as status union all
select 8 as id, 145 as val, 'Test4' as name, cast('2020-01-28 07:47:00' as datetime) as dt, 'close' as status
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;
| val | name | o_gate | o_dt | c_gate | c_dt |
|---|---|---|---|---|---|
| 145 | Test1 | open | 2020-01-28 02:18:00 | null | null |
| 145 | Test2 | open | 2020-01-28 04:10:00 | null | null |
| 145 | Test3 | open | 2020-01-28 05:50:00 | close | 2020-01-28 05:56:00 |
| 145 | Test4 | open | 2020-01-28 07:36:00 | close | 2020-01-28 07:47:00 |