MySQL – Rows to Columns Conversion

In this post, we will learn how to convert Rows to columns and columns into row 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 row into column, the columns will become the row 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 row should be converted as columns and column as row. 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 – Row to Column Conversion: by stack over flow Mysql Tables.

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