How to create mysql pivot table

Question

I would like to make a pivot table out of tracking_table from my database have the activity as column headings and the date_added as data of that column but at the moment I am not getting the desired result. More insights from you is very much appreciated.

See below

TRACKING TABLE RAW DATA BEFORE PIVOT QUERY TABLE 1

 labref    |       activity              |      date_added
----------------------------------------------------------
NDQD201511540   Issuing                         25-01-2016  
NDQD201511540   Returning to Supervisor         29-01-2016 
NDQD201511540   Returning to Documentation      08-02-2016  
NDQD201511540   Returning to Documentation      12-02-2016  
NDQD201511540   Returning for COA Drafting      15-02-2016  
NDQD201511540   Assigning COA Draft for Review  16-02-2016  
NDQD201511540   Forwarding COA for Approval     18-02-2016  
NDQD201511540   Authorization of COA Release    22-02-2016  
NDQD201511540   CAN No.                         CAN/2015-16/354

MY SQL QUERY producing TABLE 3

SELECT labref,   
(CASE WHEN activity='Issuing' THEN date_added ELSE NULL END) AS 'ISS',
(CASE WHEN activity='Returning to Supervisor' THEN date_added ELSE NULL END) AS 'RBS',
(CASE WHEN activity='Assigning for worksheet Review' THEN date_added ELSE NULL END) AS 'SWFR',
(CASE WHEN activity='Assigning COA Draft for Review' THEN date_added ELSE NULL END) AS 'SCDR',
(CASE WHEN activity='Forwarding COA for Approval' THEN date_added ELSE NULL END) AS 'DSTD',
(CASE WHEN activity='Authorization of COA Release' THEN date_added ELSE NULL END) AS 'DDSD',
(CASE WHEN activity='CAN No.' THEN date_added ELSE NULL END) AS 'CANo.'   
FROM tracking_table
WHERE labref ='NDQD201511540'
GROUP BY labref

Result without GROUP BY labref
TABLE 2

labref   |  ISS     |   RBS     |   SWFR |  SCDR   |   DSTD  |    DDSD  |     CANo. 
----------------------------------------------------------------------------------------    
NDQD201511540   25-01-2016 NULL         NULL    NULL       NULL       NULL        NULL
NDQD201511540   NULL       29-01-2016   NULL    NULL       NULL       NULL        NULL
NDQD201511540   NULL       NULL         NULL    NULL       NULL       NULL        NULL
NDQD201511540   NULL       NULL         NULL    NULL       NULL       NULL        NULL
NDQD201511540   NULL       NULL         NULL    NULL       NULL       NULL        NULL
NDQD201511540   NULL       NULL         NULL    16-02-2016 NULL       NULL        NULL
NDQD201511540   NULL       NULL         NULL    NULL       18-02-2016 NULL        NULL
NDQD201511540   NULL       NULL         NULL    NULL       NULL       22-02-2016  NULL
NDQD201511540   NULL       NULL         NULL    NULL       NULL       NULL        CAN/2015-16/354

Result GROUP BY labref TABLE 3

    labref   |  ISS     |   RBS     |   SWFR |  SCDR   |   DSTD  |    DDSD  |     CANo. 
----------------------------------------------------------------------------------------    
NDQD201511540   25-01-2016 NULL         NULL    NULL       NULL       NULL        NULL

Expected Result (THIS IS WHAT I WANT TO ACHIEVE) TABLE 4

labref       |  ISS     |      RBS    |    SWFR    |    SCDR     |      DSTD     |     DDSD     |   CANo. 
----------------------------------------------------------------------------------------------------------------------  
NDQD201511540   25-01-2016     29-01-2016      NULL     16-02-2016      18-02-2016      22-02-2016    CAN/2015-16/354

When using an EAV model, consider storing values according to data type. I like to have separate tables for dare type values, decimal type values, and strings. Also, consider handling issues of data display in application code. — Strawberry, Nov 23 '17 at 08:24

score 0 · Answer 1 · answered Nov 23 '17 at 08:13

you can use group value and for this you must use a (fake) aggregation function for avoid unpredictable result

SELECT labref,   
  MAX(CASE WHEN activity='Issuing' THEN date_added ELSE NULL END) AS 'ISS',
  MAX(CASE WHEN activity='Returning to Supervisor' THEN date_added ELSE NULL END) AS 'RBS',
  MAX(CASE WHEN activity='Assigning for worksheet Review' THEN date_added ELSE NULL END) AS 'SWFR',
  MAX(CASE WHEN activity='Assigning COA Draft for Review' THEN date_added ELSE NULL END) AS 'SCDR',
  MAX(CASE WHEN activity='Forwarding COA for Approval' THEN date_added ELSE NULL END) AS 'DSTD',
  MAX(CASE WHEN activity='Authorization of COA Release' THEN date_added ELSE NULL END) AS 'DDSD',
  MAX(CASE WHEN activity='CAN No.' THEN date_added ELSE NULL END) AS 'CANo.'   
FROM tracking_table
WHERE labref ='NDQD201511540'
GROUP BY labref

How to create mysql pivot table

1 Answers1