SQLize Online / PHPize Online  /  SQLtest Online

create table t ( id int primary key auto_increment, employee_id int, office_id int ); insert into t values (1, 1, 1), (2, 2,1),(3,3,1); select office_id, json_arrayagg(employee_id) employee_ids from t group by office_id ; SELECT ANY_VALUE(id) AS 'id', office_id, IF( POSITION(',' IN GROUP_CONCAT(employee_id))=0, GROUP_CONCAT(employee_id), SUBSTR(GROUP_CONCAT(employee_id), 1, POSITION(',' IN GROUP_CONCAT(employee_id))-1) ) AS 'employee_id_first', IF( POSITION(',' IN GROUP_CONCAT(employee_id))=0, NULL, SUBSTR( RIGHT(GROUP_CONCAT(employee_id), LENGTH(GROUP_CONCAT(employee_id))-POSITION(',' IN GROUP_CONCAT(employee_id))), 1, POSITION(',' IN RIGHT(GROUP_CONCAT(employee_id), LENGTH(GROUP_CONCAT(employee_id))-POSITION(',' IN GROUP_CONCAT(employee_id))))-1 ) ) AS 'employee_id_second' FROM t GROUP BY office_id;