How to Convert Rows to Columns in MySQL

  2. HowTo
  3. MySQL Howtos
  4. How to Convert Rows to Columns in MySQL
Preet Sanghavi Feb 02, 2024
MySQL MySQL Row MySQL Column
  1. Create a Dataset in MySQL
  2. Convert Rows to Columns in MySQL Using the CASE Statement
  3. Convert Rows to Columns in MySQL Using the IF Statement
How to Convert Rows to Columns in MySQL

In this tutorial, we will learn how to change rows to columns of a particular table in MySQL.

Create a Dataset in MySQL

It is frequently required to obtain the appropriate information connected with the necessary row data displayed as columns. Let us understand how to get this done.

However, before we begin, we will create a dummy dataset to work with. Here, we will create a table, student_details, along with 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 above query creates a table and rows with the students’ first and last names. To view the entries in the data, we will use the following code:

SELECT * FROM student_details;

The above code would 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 the CASE Statement

The basic syntax of the CASE technique can be illustrated 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 whether the students with the names Preet, Rich, and Veron are present in the class or not. Moreover, we will try to display the results such that rows are converted to columns using the abovementioned technique.

This operation 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 query above, we aim to convert the rows to columns as Preet_present, Rich_present, and Veron_present. The output of the query mentioned above is illustrated as follows.

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

The code above counts the number of distinct stu_id from the student_details table, given that stu_firstName ends with reet in the IF clause. The output of the code above 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

Note: The above code used the alias Preet_Present, Rich_Present, and Veron_Present with the AS keyword.

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

Convert Rows to Columns in MySQL Using the IF Statement

An IF statement filters data based on a particular condition or a set of conditions in MySQL. The basic syntax of converting rows to columns in MySQL using the IF statement can be illustrated as follows.

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

As the query above shows, we performed the do_this operation when the IF condition returns True. When the IF condition returns False, we perform the else_do_this operation.

We can use the query below for our student_details table to get the desired result.

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 query above can be illustrated 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

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

Author: Preet Sanghavi
Preet Sanghavi avatar Preet Sanghavi avatar

Preet writes his thoughts about programming in a simplified manner to help others learn better. With thorough research, his articles offer descriptive and easy to understand solutions.

LinkedIn GitHub

Related Article - MySQL Row

Related Article - MySQL Column