In this article, we will be looking into how to convert rows to columns. There is often a requirement where we want to get the row data represented as columns and get the necessary information associated with them.
Table of Contents
- Introduction
- MySQL: convert rows to columns – using CASE
- MySQL: convert rows to columns – using IF
- MySQL: convert rows to columns for extensive data – using prepared statements
Introduction
MySQL does not provide any PIVOT function to set this view up for us. We can use CASE statements along with aggregated functions to see how to transpose rows to columns.
Let us get started by setting up our sample data to be used across. We will be creating a table named students_marks and inserting rows into it.
CREATE TABLE students_marks ( id INT AUTO_INCREMENT, student_id INT, student_name VARCHAR(255), term VARCHAR(50), marks INT, student_subject VARCHAR(50), PRIMARY KEY (id) ); INSERT INTO students_marks (student_id,student_name,term,marks,student_subject) VALUES(1,"Gustav","term1",50,"Physics"), (1,"Gustav","term2",50,"Physics"), (1,"Gustav","term3",54,"Physics"), (1,"Gustav","term1",76,"Chemistry"), (1,"Gustav","term2",50,"Chemistry"), (1,"Gustav","term3",34,"Chemistry"), (1,"Gustav","term1",96,"Botany"), (1,"Gustav","term2",90,"Botany"), (1,"Gustav","term3",74,"Botany"), (2,"Henric","term1",34,"Physics"), (2,"Henric","term2",45,"Physics"), (2,"Henric","term3",90,"Physics"), (2,"Henric","term1",94,"Chemistry"), (2,"Henric","term2",45,"Chemistry"), (2,"Henric","term3",94,"Chemistry"), (2,"Henric","term1",23,"Botany"), (2,"Henric","term2",45,"Botany"), (2,"Henric","term3",56,"Botany"), (3,"Rasmus","term1",40,"Physics"), (3,"Rasmus","term2",56,"Physics"), (3,"Rasmus","term3",23,"Physics"), (3,"Rasmus","term1",76,"Chemistry"), (3,"Rasmus","term2",56,"Chemistry"), (3,"Rasmus","term3",33,"Chemistry"), (3,"Rasmus","term1",89,"Botany"), (3,"Rasmus","term2",67,"Botany"), (3,"Rasmus","term3",90,"Botany");
Let us see what got added into the table students_marks by executing:
Frequently Asked:
SELECT * FROM students_marks;
Output:-

MySQL: convert rows to columns – using CASE
The requirement is to see the sum of marks for each student for all the terms per subject.
It will be apparent if we could see columns per student_names. Observe the below query.
SELECT student_subject, SUM(CASE WHEN student_name = "Gustav" THEN marks ELSE 0 END) AS Gustav, SUM(CASE WHEN student_name = "Henric" THEN marks ELSE 0 END) AS Henric, SUM(CASE WHEN student_name = "Rasmus" THEN marks ELSE 0 END) AS Rasmus FROM students_marks GROUP BY student_subject;
Output:-

The output in figure 2 shows that columns have been created corresponding to each student_name. Here a SELECT statement is used to show the data in the desired form. We are also using CASE statements to check conditions based on student_name and get the total marks in a particular subject.
If we want this output frequently, then we can easily create a view for the same. See the below queries and output.
CREATE OR REPLACE VIEW student_total_marks_per_subject AS ( SELECT student_subject, SUM(CASE WHEN student_name = "Gustav" THEN marks ELSE 0 END) AS Gustav, SUM(CASE WHEN student_name = "Henric" THEN marks ELSE 0 END) AS Henric, SUM(CASE WHEN student_name = "Rasmus" THEN marks ELSE 0 END) AS Rasmus FROM students_marks GROUP BY student_subject ); SELECT * FROM student_total_marks_per_subject;
Output:-

The output is the same as in figure 2.
MySQL: convert rows to columns – using IF
We can also use the IF() function for the same requirement.
Get the sum of marks for each student for all the terms per subject. Observe the query.
SELECT student_subject, SUM(IF(student_name = 'Gustav', marks, 0)) AS Gustav, SUM(IF(student_name = 'Henric', marks, 0)) AS Henric, SUM(IF(student_name = 'Rasmus', marks, 0)) AS Rasmus FROM students_marks GROUP BY student_subject;
Output:-

MySQL: convert rows to columns for extensive data – using prepared statements
Get the sum of marks for each student for all the terms per subject.
It is not practical to use the same queries if there are lots and lots of rows on your table. In this case, we should let MySQL build the query for us using prepared statements. Observe the below query.
SET @sql = NULL; SELECT GROUP_CONCAT(DISTINCT CONCAT( 'ifnull(SUM(case when student_name = ''', student_name, ''' then marks end),0) AS `', student_name, '`' ) ) INTO @sql FROM students_marks; SET @sql = CONCAT('SELECT student_subject, ', @sql, ' FROM students_marks GROUP BY student_subject'); PREPARE stmt1 FROM @sql; EXECUTE stmt1;
Output:-

Here we are using GROUP_CONCAT() function to create a query by MySQL server dynamically. The query is the same as discussed earlier. Just that student_name is pulled automatically on the fly.
Finally, run the DEALLOCATE prepared statement by executing:
DEALLOCATE PREPARE stmt1;
Action Output Message:-
19:28:04 DEALLOCATE PREPARE stmt1 0 row(s) affected 0.00018 sec
We hope this article helped to understand the techniques to convert rows to columns. Good Luck !!!