MySQL: แสดงคะแนนนักเรียนในคอลัมน์เดียวตามวิชา ด้วย UNION ALL

ใช้ UNION ALL เพื่อรวมคะแนนหลายวิชาเป็นคอลัมน์เดียว (Unpivot)

ตารางต้นทาง (DemoTable729)

mysql> CREATE TABLE DemoTable729 (
    -> StudentId INT AUTO_INCREMENT PRIMARY KEY,
    -> StudentName VARCHAR(100),
    -> MySQLMarks INT, CMarks INT, JavaMarks INT
    -> );
mysql> INSERT INTO DemoTable729(StudentName, MySQLMarks, CMarks, JavaMarks) VALUES
    -> ('Chris',94,67,75), ('Robert',45,99,54), ('David',57,89,43);

ข้อมูลดิบ (Wide Format)

+-----------+-------------+------------+--------+-----------+
| StudentId | StudentName | MySQLMarks | CMarks | JavaMarks |
+-----------+-------------+------------+--------+-----------+
| 1         | Chris       | 94         | 67     | 75        |
| 2         | Robert      | 45         | 99     | 54        |
| 3         | David       | 57         | 89     | 43        |
+-----------+-------------+------------+--------+-----------+

Query: UNION ALL แปลงเป็น Long Format

mysql> SELECT StudentId, StudentName, 'MySQL' AS SubjectName, MySQLMarks AS Score FROM DemoTable729
    -> UNION ALL
    -> SELECT StudentId, StudentName, 'C' AS SubjectName, CMarks AS Score FROM DemoTable729
    -> UNION ALL
    -> SELECT StudentId, StudentName, 'Java' AS SubjectName, JavaMarks AS Score FROM DemoTable729;

ผลลัพธ์ (9 แถว: 3 คน × 3 วิชา)

+-----------+-------------+-------------+-------+
| StudentId | StudentName | SubjectName | Score |
+-----------+-------------+-------------+-------+
| 1         | Chris       | MySQL       | 94    |
| 2         | Robert      | MySQL       | 45    |
| 3         | David       | MySQL       | 57    |
| 1         | Chris       | C           | 67    |
| 2         | Robert      | C           | 99    |
| 3         | David       | C           | 89    |
| 1         | Chris       | Java        | 75    |
| 2         | Robert      | Java        | 54    |
| 3         | David       | Java        | 43    |
+-----------+-------------+-------------+-------+