mysql - 如何创建 mysql 数据透视表

Question

这个问题已经有答案了:

How can I return pivot table output in MySQL? (10 个回答)

已关闭 5 年前。

我想从我的数据库中的tracking_table中创建一个数据透视表，将事件作为列标题和 date_add 作为该列的数据，但目前我没有得到想要的结果。非常感谢您提供更多见解。

见下文

在透视查询之前跟踪表原始数据表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 

我的 SQL 查询生成表 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

没有 GROUP BY labref 的结果
表 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

结果按 labref 分组表3

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

预期结果(这就是我想要实现的)表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

Answer 1

您可以使用组值，为此您必须使用(假)聚合函数以避免不可预测的结果

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