How can I join multiple rows in just one single row through mysql?
Sno.| Name | Subjects
1. | ABC | English
2. | ABC | Mathematics
3. | ABC | Science
4. | FMC | French
5. | ABC | Russian
6. | JBC | French
Sno.| Name | Sub1 | Sub2 | Sub3 | Sub4 |
1. | ABC | Eng | Maths| Science| Russian
2. | FMC | French| Null| Null | Null
3. | JBC | French| Null | Null | Null
I agree with the other answers, that
GROUP_CONCAT along with PHP to split the comma separated values is probably the best approach, however if for any other reason you needed the output you suggested via Pure SQL I would suggest one of the following appoaches.
SELECT t1.Name, MIN(t1.Subject) AS Sub1, MIN(t2.Subject) AS Sub2, MIN(t3.Subject) AS Sub3, MIN(t4.Subject) AS Sub4 FROM Students t1 LEFT JOIN Students T2 ON t1.Name = t2.Name AND t2.Subject > t1.Subject LEFT JOIN Students T3 ON t2.Name = t3.Name AND t3.Subject > t2.Subject LEFT JOIN Students T4 ON t3.Name = t4.Name AND t4.Subject > t3.Subject GROUP BY t1.Name;
SELECT Name, MAX(IF(RowNum = 1,Subject, NULL)) AS Sub1, MAX(IF(RowNum = 2,Subject, NULL)) AS Sub2, MAX(IF(RowNum = 3,Subject, NULL)) AS Sub3, MAX(IF(RowNum = 4,Subject, NULL)) AS Sub4 FROM ( SELECT Name, Subject, @r:= IF(@Name = Name, @r + 1, 1) AS RowNum, @Name:= Name AS Name2 FROM Students, (SELECT @Name:='') n, (SELECT @r:= 0) r ORDER BY Name, Sno ) t GROUP BY Name