Move GROUP_CONCAT's comma values to columns
-
Hi, I am using below code to display times as Pivot table grouped by date, this is basically for fingerprint attendance... I am getting what I want like this: 2012-06-03 10:23:30,10:23:32,10:24:05,10:24:07,10:24:24,10:24:26 How can I make the comma separated values displayed in columns instead of comma so it will be something like this created_date - time1 - time2 - time3 - time4 --- etc this is the code:
SELECT created_date, GROUP_CONCAT(created_time)
FROM fingerprint
GROUP BY created_dateTechnology News @ www.JassimRahma.com
-
Hi, I am using below code to display times as Pivot table grouped by date, this is basically for fingerprint attendance... I am getting what I want like this: 2012-06-03 10:23:30,10:23:32,10:24:05,10:24:07,10:24:24,10:24:26 How can I make the comma separated values displayed in columns instead of comma so it will be something like this created_date - time1 - time2 - time3 - time4 --- etc this is the code:
SELECT created_date, GROUP_CONCAT(created_time)
FROM fingerprint
GROUP BY created_dateTechnology News @ www.JassimRahma.com
Ok, Please HELLLLLP :confused: 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 happoy 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[^]
Technology News @ www.JassimRahma.com
-
Ok, Please HELLLLLP :confused: 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 happoy 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[^]
Technology News @ www.JassimRahma.com
anybody can help please?
Technology News @ www.JassimRahma.com
