How to create MySQL query that converts rows to columns?

Question

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.

You are looking for pivoting. Please take a look at this https://stackoverflow.com/questions/1241178/mysql-rows-to-columns — Mova, Sep 08 '22 at 16:20
On the first glance it seems that you need MySQL emulation of `FULL OUTER SELF JOIN`. — PM 77-1, Sep 08 '22 at 16:27
I disagree with the above 2 comments. This looks like a gaps and islands problem. — Tim Biegeleisen, Sep 08 '22 at 16:37

score 2 · Accepted Answer · answered Sep 08 '22 at 18:31

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.

How to create MySQL query that converts rows to columns?

1 Answers1