How to merge two rows with same column value into one row

I'm trying to fetch data using Codeigniter 4 and query builder. I have a table structure like below

id	employee_id	in_out	time
1	EMP_001	in	08:10
2	EMP_001	out	10:30
3	EMP_002	in	09:15

I want to retrieve data and output to the view as single array, combining two rows of records into one like below

id	employee_id	in	out
1	EMP_001	08:10	10:30
2	EMP_002	09:15

my current model is below

    $builder = $this->db->table('attendance');
    $builder->select('id, employee_id, in_out, time');
    $builder->select('id, employee_id, in_out, CONCAT(DATE_FORMAT(time, "%r"), '.', DATE_FORMAT(time, "%r")) AS clock_time', FALSE);
    $builder->groupBy('employee_id');
    $query = $builder->get()->getResult();

    return $query;

I'm trying to figure out using CONCAT or is there a better way?

Solution

you can resolve this by joining (left join) your table attendance with itself.

The columns in and out are aliases from the column time. The first SELECTgets you all in values by each employee, which by your example has always a value.

Now we only need to add the out part, which is done via a LEFT JOIN, querying the table attendance again.

this is the raw MySQL query, which you can see working in this sqlfiddle

SELECT
    `t1`.`id`,
    `t1`.`employee_id`,
    `t1`.`time` AS `in`,
    `t2`.`time` AS `out`
FROM
    `attendance` `t1`
LEFT JOIN(
    SELECT
        *
    FROM
        `attendance`
    WHERE
        `in_out` = 'out'
) t2
ON
    `t1`.`employee_id` = `t2`.`employee_id`
WHERE
    `t1`.`in_out` = 'in'

this can be "translated" into a CI query using the CI Query Builder Class. Note the use of table aliases t1 and t2 as well as the column aliases in and out

$builder = $this->db->table('attendance t1');
$builder->select('t1.id, t1.employee_id, t1.time as in, t2.time as out');
$builder->join('(SELECT * FROM attendance WHERE in_out = "out") t2', 't1.employee_id = t2.employee_id', 'left')
$builder->where(t1.in_out, 'in');
$query = $builder->get()->getResult();

return $query;

while this is to answer your question How to merge two rows with same column value into one row you will need to prepare for several time attendance issues, like

multiple check-in/out from same employee at same day
check out after midnight (next day)
employee forgot to check in earlier and wants to check out
employee forgot to check out earlier and wants to check in

and probably others