Sometimes requirement arises to display a field value as column name in output and adjacent values in the same row below it as its values.
Suppose I have a table “students” with marks of each student in different subjects. Each student may not appear in every subject:
We want to show output like this:
As you can see php, java and android are data value but still they appear as column names and we show marks obtained by each student in the subject below corresponding column. Since aaa did not appear in android exam so his marks are written as 0, similarly for other 0 values.
The process of transferring the row values to column names is known as a pivot. MySQL does not have a pivot function, so we have to transform the data using an aggregate (sum, max) function with a CASE or IF expression.
We can get these finite values in two ways to apply to mysql query:
1. If we have fixed rows for specific column in given range only, then we can apply given values directly to the query (like in the query I have written below)
2. We can first get unique values from subject column (or any other column whose records we want to display as columns) by using the following query and then dynamically generate the required query of sum function:
SELECT distinct subject FROM students;
Following query will be used to convert row values into column names by using an aggregate function(sum) with case statement:
select name, sum(case when subject = 'php' then marks else 0 end) php, sum(case when subject = 'java' then marks else 0 end) java, sum(case when subject = 'android' then marks else 0 end) android from students group by name
We will get required output with this query, as below: