MySQL to Transpose Row into Column

Follow by Email
Facebook
Google+
http://xtremepassion.leladystore.com/2019/01/mysql-to-transpose-row-into-column/
YouTube
PINTEREST
INSTAGRAM

Follow below 3 simple steps to create new table, insert data and transpose the table’s row to column via MySQL.

Step 1:
Create Exams table

CREATE TABLE Exams (
    Id int unsigned not null auto_increment,
    Subject varchar(30) not null default '',
    Student varchar(30) not null default '',
    Marks int unsigned not null default 0,
    PRIMARY KEY (Id)
);

Step 2:
Insert data into Exams table

INSERT INTO Exams (Subject, Student, Marks)
VALUES  ('Biology', 'John', 80),
        ('Biology', 'Paul', 70),
        ('Biology', 'Mark', 88),
        ('Biology', 'Luke', 57),
        ('Accounting', 'John', 77),
        ('Accounting', 'Paul', 76),
        ('Accounting', 'Luke', 50),
        ('Mathematics', 'John', 90),
        ('Mathematics', 'Mark', 67);

Step 3:
Transpose Exams table’s Rows to Columns

SELECT Subject, 
    MAX(CASE WHEN Student = 'John' THEN Marks ELSE '-' END) AS John, 
    MAX(CASE WHEN Student = 'Paul' THEN Marks ELSE '-' END) AS Paul, 
    MAX(CASE WHEN Student = 'Mark' THEN Marks ELSE '-' END) AS Mark, 
    MAX(CASE WHEN Student = 'Luke' THEN Marks ELSE '-' END) AS Luke
FROM Exams
GROUP BY Subject;

Take Notes:
MAX () Function is required to read through each Student to fill in their’s marks into each column that Group By Subject. Without the MAX () Function, only Student’s mark which 1st found will be filled into the column that Group By Subject.

Leave a Reply

Your email address will not be published. Required fields are marked *

143 views