MySQL में UNION ALL का उपयोग करके विषयवार अंकों को एकल कॉलम में प्रदर्शित करना

MySQL में एकाधिक कॉलम्स (विषयवार अंक) को एकल कॉलम में पंक्तियों के रूप में प्रदर्शित करने के लिए UNION ALL का उपयोग करें। यह "अनपिवट" (unpivot) ऑपरेशन के समान है।

सिंटैक्स

SELECT id, name, 'Subject1' AS subject, subject1_marks AS score FROM table
UNION ALL
SELECT id, name, 'Subject2' AS subject, subject2_marks AS score FROM table
UNION ALL
SELECT id, name, 'Subject3' AS subject, subject3_marks AS score FROM table;

व्यावहारिक उदाहरण

1. टेबल बनाएं और डेटा डालें

mysql> CREATE TABLE DemoTable729 (
    StudentId INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    StudentName VARCHAR(100),
    MySQLMarks INT,
    CMarks INT,
    JavaMarks INT
);
Query OK, 0 rows affected (0.40 sec)

mysql> INSERT INTO DemoTable729 (StudentName, MySQLMarks, CMarks, JavaMarks) VALUES 
('Chris', 94, 67, 75),
('Robert', 45, 99, 54),
('David', 57, 89, 43);
Query OK, 3 rows affected (0.15 sec)

2. मूल डेटा (वाइड फॉर्मेट)

mysql> SELECT * FROM DemoTable729;
+-----------+-------------+------------+--------+-----------+
| StudentId | StudentName | MySQLMarks | CMarks | JavaMarks |
+-----------+-------------+------------+--------+-----------+
| 1         | Chris       | 94         | 67     | 75        |
| 2         | Robert      | 45         | 99     | 54        |
| 3         | David       | 57         | 89     | 43        |
+-----------+-------------+------------+--------+-----------+
3 rows in set (0.00 sec)

3. UNION ALL के साथ लॉन्ग फॉर्मेट में परिवर्तित

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;
+-----------+-------------+-------------+-------+
| 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    |
+-----------+-------------+-------------+-------+
9 rows in set (0.00 sec)

UNION vs UNION ALL

अनपिवट के लिए हमेशा UNION ALL उपयोग करें क्योंकि डुप्लिकेट संभव नहीं हैं (विषय नाम अलग हैं)।

MySQL 8.0+ में JSON_ARRAYAGG विकल्प

SELECT StudentId, StudentName,
    JSON_ARRAYAGG(JSON_OBJECT('subject', subj, 'score', scr)) AS marks
FROM (
    SELECT StudentId, StudentName, 'MySQL' AS subj, MySQLMarks AS scr FROM DemoTable729
    UNION ALL
    SELECT StudentId, StudentName, 'C', CMarks FROM DemoTable729
    UNION ALL
    SELECT StudentId, StudentName, 'Java', JavaMarks FROM DemoTable729
) t
GROUP BY StudentId, StudentName;

पिवट (उल्टा ऑपरेशन) के लिए

SELECT StudentId, StudentName,
    MAX(CASE WHEN SubjectName = 'MySQL' THEN Score END) AS MySQLMarks,
    MAX(CASE WHEN SubjectName = 'C' THEN Score END) AS CMarks,
    MAX(CASE WHEN SubjectName = 'Java' THEN Score END) AS JavaMarks
FROM long_format_table
GROUP BY StudentId, StudentName;