MySQL Generate Columns from Row Values

Question

I want to produce a query result for monthly attendance based on each days as columns. Here's my attendance table:

+----+-------------+------------+-----------------+-----------------+--------+
| id | employee_id | date       | clock_in        | clock_out       | detail |
+----+-------------+------------+-----------------+-----------------+--------+
|  1 |           1 | 2021-03-10 | 07:24:24.000000 | 12:55:10.000000 | NULL   |
|  2 |           1 | 2021-03-11 | 07:00:24.000000 | 14:00:25.000000 | NULL   |
|  3 |           1 | 2021-03-12 | 16:57:25.000000 | 16:57:30.000000 | NULL   |
|  4 |           1 | 2021-03-13 | NULL            | NULL            | OFF    |
|  5 |           1 | 2021-03-14 | NULL            | NULL            | OFF    |
+----+-------------+------------+-----------------+-----------------+--------+

The result I want is like this:

employee_id | 10-03-2021          | 11-03-2021          | 12-03-2021          | 13-03-2021 | 14-03-2021 
------------+---------------------+---------------------+---------------------+------------+-------------
1           | 07:24:24 - 12:55:10 | 07:00:24 - 14:00:25 | 16:57:25 - 16:57:30 | OFF        | OFF

I am trying to follow the answer from this MySQL pivot row into dynamic number of columns but I'm still unable to produce the result correctly. Here's my query statement.

SET @SQL = NULL;
SELECT
    GROUP_CONCAT( CONCAT( '(case when  date_format(date, ''%d-%m-%y'') = ''', date_format( date, '%d-%m-%y' ), ''' then concat(clock_in, '' - '', clock_out) else detail end) 
    AS `', date_format( date, '%d-%m-%y' ), '`' ) ) INTO @SQL 
FROM
    attendance;

SET @SQL = CONCAT( 'SELECT employee_id, ', @SQL, ' FROM attendance
GROUP BY employee_id' );
PREPARE stmt 
FROM
@SQL;
EXECUTE stmt;

Here's my Table Structure:

CREATE TABLE `attendance` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `employee_id` int(11) DEFAULT NULL,
  `date` date DEFAULT NULL,
  `clock_in` time(6) DEFAULT NULL,
  `clock_out` time(6) DEFAULT NULL,
  `detail` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

insert into attendance (employee_id, date, clock_in, clock_out, detail) values (1, '2021-03-10', '07:24:24', '12:55:10', null);
insert into attendance (employee_id, date, clock_in, clock_out, detail) values (1, '2021-03-11', '07:00:24', '14:00:25', null);
insert into attendance (employee_id, date, clock_in, clock_out, detail) values (1, '2021-03-12', '16:57:25', '16:57:30', null);
insert into attendance (employee_id, date, clock_in, clock_out, detail) values (1, '2021-03-13', null, null, 'OFF');
insert into attendance (employee_id, date, clock_in, clock_out, detail) values (1, '2021-03-14', null, null, 'OFF');

Any ideas would be greatly appreciated. Thank you.

MySQL Generate Columns from Row Values

0 Answers0