How to create column instead of concatenating string in MySQL?

Question

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.

Bill Karwin · Accepted Answer · 2021-10-02 03:06:26Z

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;

answered Oct 2, 2021 at 3:06

Bill Karwin

567k87 gold badges711 silver badges872 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to create column instead of concatenating string in MySQL?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related