mysql - 使用 MySQL key 转置

Question

我需要转置表，但特别是在键 ID 上，本质上我需要一个 GROUP BY 来根据表中已存在的列创建新列。为了澄清起见，我在下面举了几个例子。

我有一个如下所示的表格:

ID,  Dates,                State
'1','2015-10-01 21:20:40','Arrived'
'1','2015-10-01 21:21:40','Pulling In'
'1','2015-10-01 21:31:40','Unloading'
'1','2015-10-01 21:50:48','Finished Unloading'
'1','2015-10-01 21:55:48','Pulled Out'
'2','2015-10-01 19:22:03','Arrived'
'2','2015-10-01 19:23:03','Pulling In'
'2','2015-10-01 19:33:03','Unloading'
'2','2015-10-01 19:47:41','Finished Unloading'
'2','2015-10-01 19:52:41','Pulled Out'
'3','2015-10-01 12:32:27','Arrived'
'3','2015-10-01 12:33:27','Pulling In'
'3','2015-10-01 12:43:27','Unloading'
'3','2015-10-01 13:03:08','Finished Unloading'
'3','2015-10-01 13:08:08','Pulled Out'

我想通过 ID 列转置，使得:

ID, Arrived_date, Pulling_In_Date, Unloading_Date, Finished_Unloading_Date, Pulled_Out_Date
'1','2015-10-01 21:20:40','2015-10-01 21:21:40','2015-10-01 21:31:40', '2015-10-01 21:50:48','2015-10-01 21:50:48' 
'2','2015-10-01 19:22:03','2015-10-01 19:23:03','2015-10-01 19:33:03', '2015-10-01 19:33:03', '2015-10-01 19:52:41'
'3','2015-10-01 12:32:27','2015-10-01 12:33:27','2015-10-01 12:43:27', '2015-10-01 13:03:08','2015-10-01 13:08:08'

到目前为止我已经有了

SELECT ID, 
(case when State = "Arrived" then Dates end) as Arrived_Date,
(case when State = "Pulling In" then Dates end) as Pulled_In_Date,
(case when State = "Unloading" then Dates end) as Unloading_Date,
(case when State = "Finished Unloading" then Dates end) as 
Finished_Unloading_Date,
(case when State = "Pulled Out" then Dates end) as Pulled_Out_Date
FROM IDDateState;`

这得到:

ID, Arrived_date, Pulling_In_Date, Unloading_Date, Finished_Unloading_Date, Pulled_Out_Date
'1','2015-10-01 21:20:40',NULL,NULL,NULL,NULL
'1',NULL,'2015-10-01 21:21:40',NULL,NULL,NULL
'1',NULL,NULL,'2015-10-01 21:31:40',NULL,NULL
'1',NULL,NULL,NULL,'2015-10-01 21:50:48',NULL
'1',NULL,NULL,NULL,NULL,'2015-10-01 21:55:48'
'2','2015-10-01 19:22:03',NULL,NULL,NULL,NULL
'2',NULL,'2015-10-01 19:23:03',NULL,NULL,NULL
'2',NULL,NULL,'2015-10-01 19:33:03',NULL,NULL
'2',NULL,NULL,NULL,'2015-10-01 19:47:41',NULL
'2',NULL,NULL,NULL,NULL,'2015-10-01 19:52:41'
'3','2015-10-01 12:32:27',NULL,NULL,NULL,NULL
'3',NULL,'2015-10-01 12:33:27',NULL,NULL,NULL
'3',NULL,NULL,'2015-10-01 12:43:27',NULL,NULL
'3',NULL,NULL,NULL,'2015-10-01 13:03:08',NULL
'3',NULL,NULL,NULL,NULL,'2015-10-01 13:08:08'

我想按 TID 进行分组，但我得到:

Error Code: 1055. Expression #2 of select list is not in GROUP BY clause and contains nonaggregated column 'IDDateState.State' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

我已经寻找解决方案几个小时了，希望得到帮助，如果有更好的方法或者我做错了什么，请告诉我。谢谢

Answer 1

添加一个聚合(例如 max 或 min)，以便数据库知道如何从组中选择一个值。例如:

SELECT  ID
,       max(case when State = "Arrived" then Dates end) as Arrived_Date
,       max(case when State = "Pulling In" then Dates end) as Pulled_In_Date
,       max(case when State = "Unloading" then Dates end) as Unloading_Date
,       max(case when State = "Finished Unloading" then Dates end)
            as Finished_Unloading_Date
,       max(case when State = "Pulled Out" then Dates end) as Pulled_Out_Date
FROM    IDDateState
GROUP BY
        ID;