如何转MySQL表行转换成列

问题描述：

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

现在我想回到一个基于一周的像这样的一天归类的结果集：

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

这是我当前的查询。但它只会返回每一天的第一拳。

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;

有人可以帮助我吗？感谢迈克

[http://stackoverflow.com/questions/14167719/how-to-transpose-mysql-rows- and-repeat-column-headers] [1] [http：// stackoverflo w.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

答

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

输出

╔═══════════╦═════════════╦═════════════╦═════════════╦═════════════╗ 
║ 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 ║ 
╚═══════════╩═════════════╩═════════════╩═════════════╩═════════════╝

太棒了！谢谢JW！ – dmikester1

我猜这是行不通的，如果每天有多个PunchEvent？ – dmikester1

你的意思是“每天每次PunchEvent的倍数”？ –

如何转MySQL表行转换成列

相关推荐