How to merge 2 records into one by attribute?

Question

MySQL

I don't know if I phrased my question correctly, but I hope you understand from the pictures.
I know what I want, but how to formulate it correctly, based on SQL terms - xs, so a Google search did not give any results.

There is a table T (1 fig.). It has 2 entries referring to the same T.office_id. How to compose a query so that it turns out like in Figure 2? It is necessary that the output already has one record with new columns T.employee_id_first and T.employee_id_second and, accordingly, T.office_id





I will be glad for any help.

Answer 1

You can directly get an array from the base:

select 
  office_id,
  json_arrayagg(employee_id) employee_ids
from t 
group by office_id;

MySQL json_arrayagg fiddle

+===========+==============+
| office_id | employee_ids |
+===========+==============+
| 1         | [1, 2]       |
+-----------+--------------+