MySQL: convert rows to columns

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:

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 !!!