How to create column instead of concatenating string in MySQL?

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

result set:

   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.

Answers:

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.

Method 1

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;

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0