MySQL 数据透视表动态行

Question

我需要你的帮助。

我有一张如下所示的表格；


+-------+-----------+-----------+-----+--------+-------------+
| 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 |


请帮助我使用 SQL，该 SQL 将从上面的表中生成下面的结果表。


<b>grade</b>|<b>s1_fname</b> |<b>s1_age</b>|<b>s1_gender</b>|<b>s2_fname</b> |<b>s2_age</b>|<b>s2_gender</b>
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 |


其中 s1 表示学生 1。 1 应该从 Student_num 列中获取

其中 s2 表示学生 2。 2 应该从 Student_num 列中获取...

我的数据库服务器是MySQL。

谢谢!

我了解到此类表格称为 PIVOT 表。

我已经尝试过下面的代码

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;

Answer 1

SELECT s1.grade, s1.firstname AS s1_fname, s1.age AS s1_age, s1.gender AS s1_gender, 
       s2.firstname AS s2_fname, s2.age AS s2_age, s2.gender AS s2_gender
FROM students s1 JOIN students s2 ON s1.student_num = 1 
     AND s2.student_num = 2 AND s1.grade = s2.grade
ORDER BY s1.grade;