I have the Vehicles Entry & Exit Record but I need to make them into a single row with column exit_time & entry_time based on the timestamp.

I tried to use the JSON_ARRAYAGG() but didn't get the results my way.

Table look like

What I need to do is make them into a single based on timestamp as entry-exit pair

CodePudding user response：

You can use LEFT JOIN on the same table, let's call it vehicle_actions. One tricky part is to identify the correct EXIT for multiple ENTRIES from the same vehicle.

The next query is identifying the entries and is looking for it, the minimum exit timestamp:

SELECT v1.id, v1.vehicle_number, v1.timestamp AS entry_time, MIN(v2.timestamp) AS exit_time 
FROM vehicle_actions AS v1
LEFT JOIN vehicle_actions AS v2 ON v1.vehicle_number = v2.vehicle_number AND v2.action = 'EXIT' AND v2.id > v1.id
WHERE v1.action = 'ENTRY'
GROUP BY v1.id, v1.vehicle_number, v1.timestamp
;

CodePudding user response：

I run a SQL query based on your table data. This May Help you a lots

select id,vehicle_number,timestamp, max(case when action = 'ENTRY' then timestamp 
end) as entry_time,
max(case when action = 'EXIT' then timestamp end) as exit_time
from table_name
group by vehicle_number

Note : kaka is table name

You can see The Real Result :

If You want to ORDER BY id just simply add

ORDER BY id

Else you want to ORDER BY timestamp just add

ORDER BY timestamp

after end of the operation.