MySQL Pivot Table Dynamic Rows

Question

I have a table like the one below:

+-------+-----------+-----------+-----+--------+-------------+
| grade | firstname | lastname | age | gender | student_num |
+-------+-----------+-----------+-----+--------+-------------+
| 2 | Stephen | Harper | 12 | male | 1 |
| 1 | Jennifer | Solomon | 10 | female | 1 |
| 4 | James | Barney | 9 | female | 1 |
| 3 | Collins | Balmer | 8 | female | 1 |
| 4 | Kehinde | Adefemi | 12 | male | 2 |
| 2 | Benjamin | Salem | 14 | female | 2 |
| 3 | Praise | Olawale | 9 | male | 2 |
| 1 | Janet | Pelumi | 7 | male | 2 |
| 3 | Ire | Adora | 11 | female | 3 |
| 2 | Manny | Grace | 13 | male | 3 |
| 2 | Esther | Benson | 7 | female | 4 |
| 3 | Stan | Collimore | 6 | female | 4 |

Please help me with the SQL that will generate the resulting table below from the one above.

grade|s1_fname |s1_age|s1_gender|s2_fname |s2_age|s2_gender|
 1 | Jennifer | 10 |female | Janet | 7 | male |
 2 | Stephen | 12 |male | Benjamin| 14 | female |
 3 | Collins | 8 |female | Praise | 9 | male |
 4 | Kehinde | 12 |male | Myedan | 8 | male |

where s1 means student1. The 1 should be gotten from from student_num column

where s2 means student2. The 2 should be gotten from from student_num column ...

My database server is MySQL.

I Have tried the code below

SET @sql = NULL;
SELECT
 GROUP_CONCAT(DISTINCT
 CONCAT(
 'SUM(CASE WHEN `grade` = ',
 `grade`,
 ' THEN firstname ELSE 0 END) AS `s',
 `student_num`, '_fname`'
 )
 ) INTO @sql
FROM students;
SET @sql = CONCAT('SELECT grade, ', @sql, ' FROM students GROUP BY grade');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

• Masoud: is this an assignment question? sounds like someone cheating :p
• manglide: @Masoud it isn't please. can you help me?
• Masoud: I will only if you tell me what is the query trying to achieve? I mean the relation between the result and the raw table.
• manglide: @masoud, i want to GROUP BY grade, and for each grade all students for that grade should be on the GROUPED BY grade row. The final destination is a CSV file, and its a client request. Also i have posted the code i have tried above.Thanks

Answer 1

You could do something along the following lines:

select 
 s1.grade, 
 s1.firstname as s1_fname, 
 s2.firstname as s2_fname, 
 s3.firstname as s3_fname,
 s4.firstname as s4_fname
from #grades as s1
-- Self join the table for second student
left outer join grades as s2
 on s1.grade = s2.grade 
 and s2.student_num = 2
-- Self join the table for third student
left outer join grades as s3
 on s1.grade = s3.grade 
 and s3.student_num = 3
-- Self join the table for forth student
left outer join grades as s4
 on s1.grade = s4.grade 
 and s4.student_num = 4
where s1.student_num = 1
order by s1.grade;

This joins the table to itself once for each set of columns (s1, s2, etc.). You could write some dynamic SQL to generate this based on the maximum student_num or just run select max(student_num) and make sure you have enough columns to catch all the values. (If you don't you'll need to add another join like the others).

Edit: This assumes that the grade and student_num columns combine to create a unique key. If you have duplicates (e.g. two students with grade 1, student_num 1), then you'll get duplicate rows in your results.