How to Pivot Table in MySQL

1. Create Pivot Table in MySQL Using MAX Function and IF Statement
2. Create Pivot Table in MySQL Using a CASE Statement
3. Create Pivot Table in MySQL Using Dynamic Pivot Columns

In this article, we describe how to transform table data from rows to columns. This process is called pivoting. The result of this transformation is usually a summary table where we present information suitable for report generation.

In MySQL, there is no built-in function to create pivot tables, so you’ll have to write a MySQL query to generate a pivot table. Fortunately, there are three different ways to create a pivot table using MySQL.

1. Create Pivot Table in MySQL using IF statement
2. Create Pivot Table in MySQL using a CASE statement
3. Create Pivot Table in MySQL using Dynamic Pivot Columns

The following script creates a table student with three columns (name, subjectid, and marks).

create table student(name varchar(20),subjectid int(10), marks int(10));

Here, we insert sample data values into the student table for the demonstration.

insert into student values ('Sam',1,70);
insert into student values ('Sam',2,77);
insert into student values ('Sam',3,71);
insert into student values ('Sam',4,70);
insert into student values ('Sam',1,99);
insert into student values ('John',1,89);
insert into student values ('John',2,87);
insert into student values ('John',3,88);
insert into student values ('John',4,89); 
insert into student values ('Martin',1,60);
insert into student values ('Martin',2,47);
insert into student values ('Martin',3,68);
insert into student values ('Martin',4,39);

After inserting these values, let us write a select query to see records in the database.

Select * from student;

The output will be:

Create Pivot Table in MySQL Using MAX Function and IF Statement

This is a very straightforward approach to the problem. In this approach, we will use the MAX function and the IF statement. If multiple entries for a single subject is present for a student in the database, it will take a maximum of two marks.

Let us now see how to use this with SELECT query:

SELECT name,
MAX(IF(subjectid=1, marks, NULL)) AS Sub1,
MAX(IF(subjectid=2, marks, NULL)) AS Sub2,
MAX(IF(subjectid=3, marks, NULL)) AS Sub3,
MAX(IF(subjectid=4, marks, NULL)) AS Sub4
FROM student
GROUP BY name;

The output will be:

This output is a compiled result for each student subject-wise. Since you want one row for each student, you need to group by the name column. Also, you need to specify one condition for each column, that is, one condition per subject.

Create Pivot Table in MySQL Using a CASE Statement

This is also a straightforward approach to the problem. We will use the MAX function along with the CASE statement in this approach. Also, if multiple entries for a single subject is present for a student in the database, it will take a maximum of two marks. Similarly, if marks do not exist for some students, it will take NULL value.

Let us now see how to use this with the SELECT query:

SELECT name,
MAX(CASE WHEN subjectid=1 THEN marks ELSE NULL END) AS Sub1,
MAX(CASE WHEN subjectid=2 THEN marks ELSE NULL END) AS Sub2,
MAX(CASE WHEN subjectid=3 THEN marks ELSE NULL END) AS Sub3,
MAX(CASE WHEN subjectid=4 THEN marks ELSE NULL END) AS Sub4
FROM student
GROUP BY name; 

The output will be:

Create Pivot Table in MySQL Using Dynamic Pivot Columns

An aggregate function (MAX), IF statement, and CASE statement generated the pivot table in the example above. The drawback of using that approach is that we need to know the column headings while writing the query, and when the number of columns increases, so does the code. We can go for both the above approaches for smaller results and all possible values.

So, to overcome these limitations, we can use dynamic pivot columns. Here, the GROUP_CONCAT function can dynamically generate the columns of a PIVOT table output.

SET @sql = NULL;
SELECT
GROUP_CONCAT(DISTINCT CONCAT(
 ' MAX(CASE WHEN subjectid = ', subjectid, ' THEN marks ELSE 0 END) 
 AS "', subjectid, '"')
)
INTO @sql FROM student;

SET @sql = CONCAT('SELECT name, ', @sql, 
 ' FROM student GROUP BY name');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

In a GROUP_CONCAT function, we use the DISTINCT keyword to get a unique list of marks. Otherwise, our query would give an error because the list is too long. You can write Select * from @sql to check the dynamic query at any point in time.

Now, when we execute this query, this is what we get as a result:

Note that the column headers are generated dynamically based on the values in the table and that the column header represents the subjectid.