GROUP_CONCAT's Rows to Columns

Question

I have this table with 16 rows only:

http://www.jassimrahma.com/temp/attendence_table.png

and I am using below code now to split the time of attendance into columns and getting this result:

http://www.jassimrahma.com/temp/attendence_result.png

but I am not happy with it! for example, 7th July, there is only one fingerprint so it should be only F1 but it's repeating it in F1, F3 and F4.

SELECT DATE(attendance_date_time) AS attendance_date, SUBSTRING_INDEX(SUBSTRING_INDEX(GROUP_CONCAT(TIME(attendance_date_time)), ',', 1), ',', -1) AS F1,
IF(LENGTH(GROUP_CONCAT(TIME(attendance_date_time))) - LENGTH(REPLACE(GROUP_CONCAT(TIME(attendance_date_time)), ',', '')) > 1,
SUBSTRING_INDEX(SUBSTRING_INDEX(GROUP_CONCAT(TIME(attendance_date_time)), ',', 2), ',', -1) ,NULL) AS F2,
SUBSTRING_INDEX(SUBSTRING_INDEX(GROUP_CONCAT(TIME(attendance_date_time)), ',', 3), ',', -1) AS F3,
SUBSTRING_INDEX(SUBSTRING_INDEX(GROUP_CONCAT(TIME(attendance_date_time)), ',', 4), ',', -1) AS F4
FROM employee_attendance
GROUP BY DATE(attendance_date_time);

How can I fix this please?

Thanks, Jassim

Answer 1

SUBSTRING_INDEX will repeat the value if the value doesnt not end with the respective delimiter,so just concat a comma at the end of each string

SELECT DATE(attendance_date_time) AS attendance_date, SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT(GROUP_CONCAT(TIME(attendance_date_time)),','), ',', 1), ',', -1) AS F1_time,
IF(LENGTH(GROUP_CONCAT(TIME(attendance_date_time))) - LENGTH(REPLACE(GROUP_CONCAT(TIME(attendance_date_time)), ',', '')) > 1,
SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT(GROUP_CONCAT(TIME(attendance_date_time)),','), ',', 2), ',', -1), NULL) AS F2_time,
-- SUBSTRING_INDEX(SUBSTRING_INDEX(GROUP_CONCAT(TIME(attendance_date_time)), ',', 3), ',', -1) AS F3_time,
IFNULL(SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT(GROUP_CONCAT(TIME(attendance_date_time)),','), ',', 3), ',', -1), NULL) AS F3_time,
SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT(GROUP_CONCAT(TIME(attendance_date_time)),','), ',', 4), ',', -1) AS F4_time
FROM employee_attendance
GROUP BY DATE(attendance_date_time);

FIDDLE