JIYIK CN >

Summary: in this tutorial, we will learn how to change rows to columns of a specific table in MySQL.

Creating a Dataset in MySQL

It is often necessary to get the appropriate information related to the necessary row data displayed as columns. Let us understand how this can be done.

However, before we begin, we will create a dummy dataset to work with. Here, we will create a table student_details and a few rows.

-- create the table student_details
CREATE TABLE student_details(
  stu_id int,
  stu_firstName varchar(255) DEFAULT NULL,
  stu_lastName varchar(255) DEFAULT NULL,
  primary key(stu_id)
);
-- insert rows to the table student_details
INSERT INTO student_details(stu_id,stu_firstName,stu_lastName)
 VALUES(1,"Preet","Sanghavi"),
 (2,"Rich","John"),
 (3,"Veron","Brow"),
 (4,"Geo","Jos"),
 (5,"Hash","Shah"),
 (6,"Sachin","Parker"),
 (7,"David","Miller");

The query above creates a table and rows containing the first and last names of students. To view the entries in the data, we will use the following code:

SELECT * FROM student_details;

The above code will give the following output:

stu_id  stu_firstName   stu_lastName
1         Preet         Sanghavi
2         Rich          John
3         Veron         Brow
4         Geo           Jos
5         Hash          Shah
6         Sachin        Parker
7         David         Miller

Convert rows to columns in MySQL using CASE statement

The basic syntax of CASE technique can be described as follows.

SELECT
column_name
    SUM(CASE WHEN stu_firstName = "Preet" THEN 1 ELSE 0 END) AS Preet_Present
   FROM
    table_name
    GROUP BY
column_name;

Now, let us check if there are students named Preet, Rich and Veron in the class. Also, we will try to display the result so as to convert rows to columns using the above technique.

This can be done using the following query,

SELECT
stu_firstName,
    SUM(CASE WHEN stu_firstName = "Preet" THEN 1 ELSE 0 END) AS Preet_Present,
    SUM(CASE WHEN stu_firstName = "Rich" THEN 1 ELSE 0 END) AS Rich_Present,
    SUM(CASE WHEN stu_firstName = "Veron" THEN 1 ELSE 0 END) AS Veron_Present
   FROM
    student_details
    GROUP BY
stu_firstName;

As we can see, in the above query, we aim to convert rows into columns like Preet_present, Rich_present, and Veron_present. The output of the above query is shown in the following figure.

SELECT DISTINCT
    COUNT(DISTINCT IF(stu_firstName like '%reet',
            stu_id,
            NULL)) AS count_student_ids
FROM student_details;

Assuming that stu_firstName ends with reet in the IF clause, the above code counts the number of distinct stu_id in the student_details table. The output of the above code is as follows:

stu_firstName   Preet_Present   Rich_Present Veron_Present
Preet           1                   0           0
Rich            0                   1           0
Veron           0                   0           1
Geo             0                   0           0
Hash            0                   0           0
Sachin          0                   0           0
David           0                   0           0

An alternative to the CASE technique is the IF technique, which can help us achieve similar results.

Convert rows to columns in MySQL using IF statement

The IF statement filters data based on a specific condition or a set of conditions in MySQL. The basic syntax of using the IF statement to convert rows to columns in MySQL is shown below.

SELECT
column_name,
    SUM(IF(column_name= "something", do_this, else_do_this)) AS Preet_Present
   FROM
    table_name
    GROUP BY
column_name;

As shown in the above query, we execute the do_this action when the IF condition returns True. When the IF condition returns False, we execute the else_do_this action.

We can use the following query on our student_details table to get the desired results.

SELECT
stu_firstName,
    SUM(IF(stu_firstName = "Preet", 1, 0)) AS Preet_Present,
    SUM(IF(stu_firstName = "Rich",1,0)) AS Rich_Present,
    SUM(IF(stu_firstName = "Veron", 1,0)) AS Veron_Present
   FROM
    student_details
    GROUP BY
stu_firstName;

The output of the above query can be explained as follows.

stu_firstName   Preet_Present   Rich_Present Veron_Present
Preet           1                   0           0
Rich            0                   1           0
Veron           0                   0           1
Geo             0                   0           0
Hash            0                   0           0
Sachin          0                   0           0
David           0                   0           0

So, with the help of CASE and IF statements, we can efficiently convert rows to columns in MySQL.