Display the student marks in a single column on the basis of subject in MySQL?

MySQLMySQLi Database

For this, use UNION ALL.

Let us first create a table:

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)

Insert some records in the table using insert command:

mysql> insert into DemoTable729(StudentName,MySQLMarks,CMarks,JavaMarks) values('Chris',94,67,75);
Query OK, 1 row affected (0.21 sec)
mysql> insert into DemoTable729(StudentName,MySQLMarks,CMarks,JavaMarks) values('Robert',45,99,54);
Query OK, 1 row affected (0.18 sec)
mysql> insert into DemoTable729(StudentName,MySQLMarks,CMarks,JavaMarks) values('David',57,89,43);
Query OK, 1 row affected (0.12 sec)

Display all records from the table using select statement:

mysql> select *from DemoTable729;

This will produce the following output -

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

Following is the query to display student marks in a single column:

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;

This will produce the following output -

+-----------+-------------+-------------+-------+
| 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)
raja
Published on 22-Aug-2019 07:40:10
Advertisements