I'm creating an employee tracking app.
I have a MySQL table which is as follows:
| ID | PersonID | TypeID | DateTime |
|---|---|---|---|
| 1 | 001 | IN | 2022-09-01T13:21:12 |
| 2 | 001 | OUT | 2022-09-01T13:25:12 |
| 3 | 001 | IN | 2022-09-01T14:21:12 |
| 4 | 001 | OUT | 2022-09-01T14:25:12 |
| 5 | 002 | IN | 2022-09-03T13:21:12 |
| 6 | 002 | OUT | 2022-09-03T13:25:12 |
| 7 | 002 | IN | 2022-09-03T14:21:12 |
| 8 | 002 | IN | 2022-09-03T14:25:12 |
| 9 | 002 | OUT | 2022-09-03T14:25:12 |
| 10 | 002 | OUT | 2022-09-03T16:25:12 |
| 11 | 002 | OUT | 2022-09-03T17:25:12 |
| 12 | 002 | IN | 2022-09-04T16:25:12 |
| 13 | 002 | IN | 2022-09-05T17:25:12 |
I would like to create a view that returns records first sorted by PersonID and then by the ID but transforms the rows into columns.
Something like this:
| PersonID | InID | In_DateTime | OutID | Out_DateTime |
|---|---|---|---|---|
| 001 | 1 | 2022-09-01T13:21:12 | 2 | 2022-09-01T13:25:12 |
| 001 | 3 | 2022-09-01T14:21:12 | 4 | 2022-09-01T14:25:12 |
| 002 | 5 | 2022-09-03T13:21:12 | 6 | 2022-09-03T13:25:12 |
| 002 | 7 | 2022-09-03T14:21:12 | null | null |
| 002 | 8 | 2022-09-03T14:25:12 | 9 | 2022-09-03T14:25:12 |
| 002 | null | null | 10 | 2022-09-03T16:25:12 |
| 002 | null | null | 11 | 2022-09-03T17:25:12 |
| 002 | 12 | 2022-09-04T16:25:12 | null | null |
| 002 | 13 | 2022-09-05T17:25:12 | null | null |
Does anyone have an idea how to do this in MySQL?
Thanks for any suggestions.
CodePudding user response：
Use window functions LEAD() or LAG() to get for each row its pair row, depending on its TypeID and do a left join of the results to the table:
WITH cte AS (
SELECT *,
CASE
WHEN TypeID = 'IN' AND LEAD(TypeID) OVER w = 'OUT' THEN LEAD(ID) OVER w
WHEN TypeID = 'OUT' AND LAG(TypeID) OVER w = 'IN' THEN LAG(ID) OVER w
END other_ID
FROM tablename
WINDOW w AS (PARTITION BY PersonID ORDER BY DateTime)
)
SELECT DISTINCT c.PersonID,
CASE WHEN c.TypeID = 'IN' THEN c.ID ELSE t.ID END InID,
CASE WHEN c.TypeID = 'IN' THEN c.DateTime ELSE t.DateTime END In_DateTime,
CASE WHEN c.TypeID = 'IN' THEN t.ID ELSE c.ID END OutID,
CASE WHEN c.TypeID = 'IN' THEN t.DateTime ELSE c.DateTime END Out_DateTime
FROM cte c LEFT JOIN tablename t
ON t.ID = c.other_ID
ORDER BY c.PersonID, COALESCE(In_DateTime, Out_DateTime);
See the demo.