How to transpose mysql table rows into columns

Question

Here is what my current mysql table looks like:

PunchID EmpID   PunchEvent  PunchDateTime
1       0456    clockin     5/14/2013 8:36:26 AM
48      0456    breakout    5/14/2013 12:01:29 PM
53      0456    breakin     5/14/2013 12:28:31 PM
54      0456    clockout    5/14/2013 2:28:33 PM
57      0456    clockin     5/15/2013 7:38:34 AM
58      0456    breakout    5/15/2013 7:38:39 AM
59      0456    breakin     5/15/2013 7:38:41 AM
60      0456    clockout    5/15/2013 7:38:42 AM

Now I want to return a result set that is grouped based on the day of the week like so:

Day       ClockIn      BreakOut    BreakIn      ClockOut
Tuesday   8:36:26 AM  12:01:29 PM  12:28:31 PM  2:28:33 PM
Wednesday 7:38:34 AM  etc, etc...

This is my current query. But it only returns the first punch for each day.

SELECT DATE_FORMAT(PunchDateTime, '%W') as day, PunchEvent, DATE_FORMAT(PunchDateTime, '%l:%m:%s %p') as time FROM timeclock_punchlog WHERE EmpID = '0456' GROUP BY DATE(PunchDateTime) ORDER BY PunchDateTime ASC;

Can someone help me with this? Thanks Mike

[http://stackoverflow.com/questions/14167719/how-to-transpose-mysql-rows-and-repeat-column-headers][1] [http://stackoverflow.com/questions/12962000/join-tables-and-transpose-columns-and-rows][2] [1]: http://stackoverflow.com/questions/14167719/how-to-transpose-mysql-rows-and-repeat-column-headers [2]: http://stackoverflow.com/questions/12962000/join-tables-and-transpose-columns-and-rows — Tynamo, May 15 '13 at 14:54

score 41 · Accepted Answer · answered May 15 '13 at 14:54

41

SELECT  DATE_FORMAT(PunchDateTime, '%W') DAY,
        MAX(CASE WHEN PunchEvent = 'ClockIn' THEN DATE_FORMAT(PunchDateTime, '%r') END) ClockIn,
        MAX(CASE WHEN PunchEvent = 'BreakOut' THEN DATE_FORMAT(PunchDateTime, '%r') END) BreakOut,
        MAX(CASE WHEN PunchEvent = 'BreakIn' THEN DATE_FORMAT(PunchDateTime, '%r') END) BreakIn,
        MAX(CASE WHEN PunchEvent = 'ClockOut' THEN DATE_FORMAT(PunchDateTime, '%r') END) ClockOut
FROM    tableName
WHERE   EmpID = 456
GROUP   BY DATE_FORMAT(PunchDateTime, '%W')
ORDER   BY PunchDateTime

SQLFiddle Demo

OUTPUT

╔═══════════╦═════════════╦═════════════╦═════════════╦═════════════╗
║    DAY    ║   CLOCKIN   ║  BREAKOUT   ║   BREAKIN   ║  CLOCKOUT   ║
╠═══════════╬═════════════╬═════════════╬═════════════╬═════════════╣
║ Tuesday   ║ 08:36:26 AM ║ 12:01:29 PM ║ 12:28:31 PM ║ 02:28:33 PM ║
║ Wednesday ║ 07:38:34 AM ║ 07:38:39 AM ║ 07:38:41 AM ║ 07:38:42 AM ║
╚═══════════╩═════════════╩═════════════╩═════════════╩═════════════╝

answered May 15 '13 at 14:54

John Woo

258,903
69
498
492

I'm guessing this would not work if there were multiple of each PunchEvent per day? – dmikester1 May 15 '13 at 15:06
1

what do you mean by `multiple of each PunchEvent per day`? – John Woo May 15 '13 at 15:06
If I had 3 'breakin' and 3 'breakout' for Tueday, would it show those? – dmikester1 May 15 '13 at 15:07
1

if you have those records, how will the output gonna look like? – John Woo May 15 '13 at 15:11
Well, I guess the number of columns would be arbitrary depending on how many breaks the user took that day. This probably isn't possible with MySQL. – dmikester1 May 15 '13 at 15:15
JW, do you know how I could calculate the TIMEDIFF between clockin and breakout as a 6th column? – dmikester1 May 15 '13 at 17:48
btw, I figured out how to do the TIMEDIFF – dmikester1 May 16 '13 at 19:52
@JohnWoo Was `Max(..)` chosen arbitrarily? By choosing `Max(..)`, you are showing the latest data. In this case, obviously, there is not enough data to make a difference. I just want to clear up whether `Max(..)` is the most suitable aggregate function here. – modulitos Jun 03 '14 at 05:52

How to transpose mysql table rows into columns

1 Answers1

Linked