I have a query:
SELECT t.emp_number, t.date_created, GROUP_CONCAT(t.time_stamp) as punch FROM attendance_copy t WHERE t.attendance_status like "%Punch%" GROUP BY t.date_created order by date_created,time_stamp
id date punch IPPH0004 | 2021-10-01 | 01:00:00,09:26:03 IPPH0004 | 2021-10-02 | 08:00:00,11:00:00
and instead of comma separated cell, I want it as a column or cell, like so:
id date in out IPPH0004 | 2021-10-01 | 01:00:00 | 09:26:03 IPPH0004 | 2021-10-02 | 08:00:00 | 11:00:00
is this possible? thanks.
Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.
If you want the columns to increase in number depending on how many rows are found in each group, then no, it’s not possible. One unchangeable quality of SQL is that you must specify the number of columns in the select-list before the query starts executing, therefore before it reads any data. So the list of columns is fixed, and won’t expand dynamically as it reads through rows.
You can make a number of expressions in the select list, as many as you expect to find in the group:
SELECT t.emp_number, t.date_created, MAX(CASE rownum WHEN 1 THEN time_stamp END) AS in, MAX(CASE rownum WHEN 2 THEN time_stamp END) AS out FROM ( SELECT emp_number, date_created, time_stamp, ROW_NUMBER() OVER (PARTITION BY date_created ORDER BY time_stamp) AS rownum FROM attendance_copy) AS t GROUP BY emp_number, date_created;