How to transpose mysql table rows into columns

by 
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

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

More Related Contents:

  1. How can I return pivot table output in MySQL?
  2. MySQL pivot row into dynamic number of columns
  3. Mysql query to dynamically convert rows to columns
  4. mysql pivot/crosstab query
  5. MySQL dynamic-pivot
  6. Mysql Convert Column to row (Pivot table )
  7. Mysql query to dynamically convert rows to columns on the basis of two columns
  8. Setting column values as column names in the SQL query result
  9. MySQL Pivot Table Column Data as Rows
  10. In SQL Server how to get the count of child if both parent and child are available [closed]
  11. Delete the entire content of all table in a Database [closed]
  12. Delete all Duplicate Rows except for One in MySQL? [duplicate]
  13. Get a list of dates between two dates
  14. Calculate a running total in MySQL
  15. What’s faster, SELECT DISTINCT or GROUP BY in MySQL?
  16. How to get the difference in years from two different dates?
  17. INSERT … ON DUPLICATE KEY (do nothing)
  18. MySQL create stored procedure syntax with delimiter
  19. Merge two tables in one SQL query and make the date values unique
  20. Using Count to find the number of occurrences
  21. Is there any reason to worry about the column order in a table?
  22. Checking a table for time overlap?
  23. Does mysql have the equivalent of Oracle’s “analytic functions”?
  24. MySQL INSERT INTO … VALUES and SELECT
  25. MYSQL select DISTINCT values in two columns
  26. Compare only day and month with date field in mysql
  27. What are practical differences between `REPLACE` and `INSERT … ON DUPLICATE KEY UPDATE` in MySQL?
  28. MySQL OPTIMIZE all tables?
  29. Strip last two characters of a column in MySQL
  30. Deleting a row based on the max value

Leave a Comment