MySQL – Rows to Columns Conversion

In this post, we will learn how to convert Rows to columns and columns into rows which we can do using transpose where each row is restored as a column for the same we have given an example where we have transposed the table.

Rows To Column Conversion

Let’s take an example of the given table that we need to transpose in MySQL.

date	    product	sales
2022-01-01	A	100
2022-01-02	A	200
2022-01-01	B	150
2022-01-02	B	250

For the same we can use the code given below to display the data as it was originally.

SELECT
  date,
  SUM( CASE WHEN product = 'A' THEN sales ELSE 0 END) AS 'A',
  SUM( CASE WHEN product = 'B' THEN sales ELSE 0 END) AS 'B'
FROM sales
GROUP BY date;

And when we convert the rows into columns, the columns will become the rows which will look like.

date     	A	B
2022-01-01	100	150
2022-01-02	200	250

We can also use different functions to get our work done as a group concat, And an example of this has being below.

SELECT
  date,
  GROUP_CONCAT( CASE WHEN product = 'A' THEN sales ELSE NULL END) AS 'A',
  GROUP_CONCAT( CASE WHEN product = 'B' THEN sales ELSE NULL END) AS 'B'
FROM sales
GROUP BY date;

So the example we have taken can be used for converting or transposing with a drawback as it would not be used for null values. We can change this behaviour by specifying a separator and using the concar was function which we have used below as an example and a pseudo code is given below.

SELECT
  date,
  CONCAT_WS(',', GROUP_CONCAT(CASE WHEN product = 'A' THEN sales ELSE NULL END)) AS 'A',
  CONCAT_WS(',', GROUP_CONCAT(CASE WHEN product = 'B' THEN sales ELSE NULL END)) AS 'B'
FROM sales
GROUP BY date;

As per the code given above the table will look like as given above after transposing the table.

As there is always another way to do the same thing here we can do the same thing using another function which is the pivot operator, Although PIVOT is not supported in MYSQL we can achieve it using different other functions which help us in getting the table transpose.

For the same, we have given an example to implement and get it done in MySQL.

Take an example of the same, same table where we need to transpose the table as per given nomes as rows should be converted as columns and columns as rows. For the same look at the example given below.

SELECT customer_id,
       SUM( CASE WHEN MONTH(order_date) = 1 THEN order_amount ELSE 0 END) AS 'January',
       SUM( CASE WHEN MONTH(order_date) = 2 THEN order_amount ELSE 0 END) AS 'February'
FROM orders
GROUP BY customer_id;

 

To learn more about MySQL – Rows to Columns Conversion: Conversion of rows in to columns in Mysql Tables.

To learn more about MySQL tutorials, and solutions to problems faced in MySQL along with Concepts visit MySQL Tutorials And Problems.

To learn more about different other programming languages and get the concepts along with the solutions to the problems we faced during solving the problems Visit: beta Python Tutorials for the solutions of problems