How to unify records in one line - MYSQL

Question

I have a teacher table and I also have a phone table where the link is through the person's ID. When I look up the phone numbers of each professor, it appears 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	PHONE	NUMBER
1	1	xxxxxxx
1	2	xxxxxxxx
1	3	xxxxxxx
2	1	xxxxxxx

However, I would like it to be shown like this:

ID_TEACHER	PHONE	NUMBER	PHONE	NUMBER	PHONE	NUMBER
1	1	xxxxx	2	xxxxx	3	xxxxx
2	1	xxxxxxxxx

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

Sadly MySQL doesn't have pivot, so you'll need to utilize some trick, like described [here](https://stackoverflow.com/questions/7674786/how-can-i-return-pivot-table-output-in-mysql) — markalex, Mar 26 '23 at 21:50

Abdulmajeed · Answer 1 · 2023-03-26T22:04:50.397

0

You need to use conditional aggregation:

Try the below:

   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

edited Mar 26 '23 at 22:04

answered Mar 26 '23 at 21:50

Abdulmajeed

1,502
2
10
13

1

You've used `P.NUMBER` as the output for every `PHONE{n}` column instead of `P.PHONE`. – Michael Mar 26 '23 at 21:53

How to unify records in one line - MYSQL

1 Answers1