Combine column from multiple rows into single row with id

Question

I have on table which store enter and exit of people in multi rows like:

EorL 1 = enter

EorL 2 = exit or leave

id | date_ |time |EorL|
1 | 150201 | 09:14 | 1 |
2 | 150201 | 09:14 | 1 |
3 | 150201 | 09:15 | 1 |
1 | 150201 | 16:07 | 2 |
2 | 150201 | 16:07 | 2 |
3 | 150201 | 17:15 | 2 |

how can combine and get:

id | date_ |enter |leave
1 | 150201 |09:14 |16:07
2 | 150201 |09:14 |16:07
3 | 150201 |09:15 |17:15

thank you in advance

Answer 1

This seems like a simple self join:

SELECT 
 enter.id,
 enter.date_ 
 enter.time AS enter_time
 leave.time AS leave_time
FROM 
 tablename AS enter
 JOIN
 tablename AS leave
 ON enter.id = leave.id
 AND enter.date_ = leave.date_
 AND leave.EorL = 2 
WHERE
 enter.EorL = 1 ;

The query assumes there are (max) 2 rows for any id and date_.

If there are cases where people have entered without leaving, use LEFT join instead. If there are people who have left without entering, use RIGHT join. If there might be both cases, use FULL join. For RIGHT and FULL, you'll have to adjust the SELECT list, using leave.id, leave.date_ and COALESCE(enter.id, leave.id), COALESCE(enter.date_, leave.date_).