MySQL – Pivot Table with Dynamic Rows

MySQLpivot

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;

Best Answer

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.

Related Question