Benjamin Lee Benjamin Lee - 2 months ago 5
MySQL Question

MySQL pivot table create activity log file with start, end time

I am new to MySQL.

I have a Set data like this.

Tester datetime_start Event Lot_Num
Tester#1 20/9/2016 13:21 Down
Tester#1 20/9/2016 13:26 LoadTP Lot_A
Tester#1 20/9/2016 13:38 LotStart Lot_A
Tester#1 20/9/2016 16:19 LotEnd Lot_A
Tester#1 20/9/2016 16:19 Idle
Tester#1 20/9/2016 16:20 Down
Tester#1 20/9/2016 16:23 LoadTP Lot_B
Tester#1 20/9/2016 16:35 LotStart Lot_B
Tester#1 20/9/2016 17:12 Down Lot_B
Tester#1 20/9/2016 17:36 LotEnd Lot_B


I Need help to make the data above to something looks like this:

Tester Lot_Num Time_Start Time_End
Tester#1 Lot_A 20/9/2016 13:26 20/9/2016 16:19
Tester#1 Lot_B 20/9/2016 16:23 20/9/2016 17:36


I have no idea how to do and I try to do something like this.

SELECT Tester,Lot_Num,Event,
(case when Event ='LoadTP' then datetime_start end)as Time_Start,
(case when Event ='LotEnd' then datetime_start end)as Time_End

from T_Utilization


But now my table looks like this.

Time_Start Time_End
Tester#1 Lot_A 20/9/2016 13:26
Tester#1 Lot_A 20/9/2016 16:19
Tester#1 Lot_B 20/9/2016 16:23
Tester#1 Lot_B 20/9/2016 17:36


Need help on Mysql, Please.

Answer

You can use conditional aggregation for this:

SELECT Tester, LotNum, 
       MAX(CASE WHEN Event ='LoadTP' THEN datetime_start END) AS Time_Start,
       MAX(CASE WHEN Event ='LotEnd' THEN datetime_start END) AS Time_End
FROM T_Utilization
WHERE LotNum IS NOT NULL
GROUP BY Tester, LotNum

Demo here