How to unify records in one row - MYSQL

Question

I have a teacher table, and a phone table where the linking is by the person's ID. When I look up each professor's phone number, it looks like this:

(SELECT
       T.ID_TEACHER,
       P.PHONE,
       P.NUMBER 
FROM TEACHER T LEFT JOIN PHONES P 
ON P.IDPERSON = T.ID_TEACHER)

ID_TEACHER	Telephone	NUMBER
1	1	xxxxxxx
1	2	xxxxxxxx
1	3	xxxxxxx
2	1	xxxxxxx

However, I want it to display like this:

ID_TEACHER	Telephone	NUMBER	Telephone	NUMBER	Telephone	NUMBER
1	1	xxxxx	2	xxxxx	3	xxxxx
2	1	xxxxxxxxx

what do I do? I'm using MYSQL. I didn't find a solution.

Answer 1

You need to use conditional aggregation:

Try the following:

SELECT
    T.ID_TEACHER,
    MAX(CASE WHEN P.PHONE = 1 THEN P.PHONE ELSE NULL END) AS PHONE1,
    MAX(CASE WHEN P.PHONE = 1 THEN P.NUMBER ELSE NULL END) AS NUMBER1,
    MAX(CASE WHEN P.PHONE = 2 THEN P.PHONE ELSE NULL END) AS PHONE2,
    MAX(CASE WHEN P.PHONE = 2 THEN P.NUMBER ELSE NULL END) AS NUMBER2,
    MAX(CASE WHEN P.PHONE = 3 THEN P.PHONE ELSE NULL END) AS PHONE3,
    MAX(CASE WHEN P.PHONE = 3 THEN P.NUMBER ELSE NULL END) AS NUMBER3
FROM TEACHER T
LEFT JOIN PHONES P ON P.IDPERSON = T.ID_TEACHER
GROUP BY T.ID_TEACHER