MySQL | How to convert row values into column names

Author - Anjana
15.09.2017
|
0 Comments
|

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:

students
1
We want to show output like this:
2
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:
3

Webner Solutions is a Software Development company focused on developing Insurance Agency Management Systems, Learning Management Systems and Salesforce apps. Contact us at dev@webners.com for your Insurance, eLearning and Salesforce applications.

Leave a Reply

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