Dynamic Pivot Table for period, Joining 2 Tables

Question

I have searched and tried solving this puzzle, all help will be appreciated...

We use an online timesheet, each location captures their own attendance. A salary month is from the 23rd till the 22nd of the following month.

The 1st table "employee", I only need the employee's name, surname and employee number (wih over a hundred employees and employees leave and new ones appointed regularly) here is an example:

employee_id employee_surname employee_first_name
SAL010  Briel   Stephanus
SAL021  Kwaza   Nakedi
SAL032  Motshabi    Kotsamere
SAL034  Nkonwana    Sibusiso
SAL056  Van Wyk Ben

The 2nd table "empl_attendance" holds the data of employee attendance, updated on a daily basis, the empl_attendance_total is the important field, but if an employee is not at work for some reason, the field is "0", then the sql must output the description in empl_attendance_start, eg "OFF":

empl_attendance_date employee_id empl_attendance_start empl_attendance_stop empl_attendance_total location_id
2012/08/23  SAL034  06:00   18:00   11  Middelkraal
2012/08/24  SAL034  06:00   18:00   11  Middelkraal
2012/08/25  SAL034  06:00   18:00   11  Middelkraal
2012/08/26  SAL010  06:00   18:00   11  Middelkraal
2012/08/23  SAL021  18:00   06:00   11  Middelkraal
2012/08/24  SAL021  18:00   06:00   11  Middelkraal
2012/08/25  SAL021  AWOL        0   Middelkraal
2012/08/23  SAL032  OFF     0   Middelkraal
2012/08/24  SAL032  OFF     0   Middelkraal
2012/08/23  SAL056  18:00   06:00   11  Middelkraal

Here location_id is important as the location is pre-selected from the previous page. The desired iutput looks something like the following and starts with the 23rd of the month till the 22nd of the following month. The period is also selected on the previous page:

employee_id employee_surname    employee_first_name 2012/08/23  2012/08/24  2012/08/25  2012/08/26
SAL010  Briel   Stephanus   11  OFF 11  11
SAL021  Kwaza   Nakedi  11  11  AWOL    11
SAL032  Motshabi    Kotsamere   OFF OFF 8   8
SAL034  Nkonwana    Sibusiso    11  11  11  OFF
SAL056  Van Wyk Ben 11  AWOL    8   8

My first time with pivot tables, my dates change from month to month and the employees may change at any given time...

score 0 · Answer 1 · edited May 23 '17 at 12:04

Unfortunately MySQL does not have a PIVOT function so you will need to use an aggregate function with CASE statements to get your final result.

To hard-code you would use something like this (not tested):

select e.id,
  e.surname,
  e.firstname,
  max(case when a.dt = '2012-08-23' then a.total end) '2012-08-23'
from employee e
inner join attendance a
  on e.id = a.emp_id
group by e.id, e.surname, e.firstname

But if you don't know the values, then you will want to use prepared statements similar to this (not tested):

SET @sql = NULL;
    SELECT
      GROUP_CONCAT(DISTINCT
        CONCAT(
          'MAX(case when yourdate = ''',
          yourdate,
          ''' then a.total END) AS ',
          yourdate
        )
      ) INTO @sql
    FROM
      Results;
    SET @sql = CONCAT('SELECT e.id,
                        e.surname,
                        e.firstname, ', @sql, ' 
                      from employee e
                      inner join attendance a
                        on e.id = a.emp_id 
                      group by e.id, e.surname, e.firstname');

    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;

Here is a working example of a dynamic pivot:

Select dynamic Columns in mysql

Dynamic Pivot Table for period, Joining 2 Tables

1 Answers1