Transpose using a key MySQL

Question

I have a need to transpose the table, but very specifically on a key ID, essentially I need a GROUP BY that makes new columns based on columns existing within a table already. I have made a few examples below for clarification.

I have a table that looks like this:

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'

I would like to transpose via the ID column, making:

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'

I have this so far

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;`

This this gets:

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'

I want to somehow group by the TID, but I get:

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

I have been searching for the solution for a few hours now and would like help, Please let me know if there is a better way or if there is something I'm doing wrong. Thank you

This is called "pivot" in databases, not "transpose". If you knew that you probably could have found the solution yourself. — Barmar, Mar 28 '18 at 18:35
When you edit, continue from a previous edit, so the formatting improvements are preserved — Andomar, Mar 28 '18 at 18:39
@Barmar - 4th pivot question today...we need a community wiki answer that we can point these questions at. — Twelfth, Mar 28 '18 at 19:04
@Twelfth Only 4th? I thought I already closed it as a duplicate. — Barmar, Mar 28 '18 at 19:21

Andomar · Accepted Answer · 2018-03-28T18:41:21.930

0

Add an aggregate (like max or min) so the database knows how to choose one value from among the group. For example:

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;

edited Mar 28 '18 at 18:41

answered Mar 28 '18 at 18:36

Andomar

232,371
49
380
404

Thank you for the quick responce. Trying the above code you presented I get: '1','20151001212040','2015-10-01 21:21:40','2015-10-01 21:31:40','2015-10-01 21:50:48','2015-10-01 21:55:48' '2','20151001192203','2015-10-01 19:23:03','2015-10-01 19:33:03','2015-10-01 19:47:41','2015-10-01 19:52:41' '3','20151001123227','2015-10-01 12:33:27','2015-10-01 12:43:27','2015-10-01 13:03:08','2015-10-01 13:08:08' – Chris Mar 28 '18 at 18:41
The first date comes out as a string loosing the -'s I'm not sure why but I'll try looking into it – Chris Mar 28 '18 at 18:41

Transpose using a key MySQL

1 Answers1