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_).