Introduction:

Data manipulation is a fundamental aspect of working with databases, and at times, it becomes necessary to transform row-wise data into columnar representations. The PIVOT operation in MySQL comes to the rescue in such scenarios, allowing developers and analysts to convert rows into columns effortlessly. In this blog, we will explore the concept of PIVOT in MySQL, demonstrate two methods for achieving this transformation, and provide real-world examples to illustrate its practical application.

Method 1: Using CASE statements

The first method involves using the CASE statement in conjunction with aggregate functions to pivot the data. Let's assume we have a table named "sales" that contains the following data:

To pivot the data and display sales for each product in different columns for each month, we can execute the following query:

SELECT
  Product,
  SUM(CASE WHEN Month = 'January' THEN Sales END) AS January,
  SUM(CASE WHEN Month = 'February' THEN Sales END) AS February,
  SUM(CASE WHEN Month = 'March' THEN Sales END) AS March
FROM sales
GROUP BY Product;

Output:

In this method, we utilized the CASE statement to conditionally evaluate the "Sales" value based on the "Month" value for each product. The SUM function was then used to aggregate the sales for each month and product combination.

Method 2: Using PIVOT clause (MySQL 8.0+)

MySQL 8.0 introduced the PIVOT clause, which simplifies the process of pivoting data, making it more intuitive and readable. Using the same "sales" table, we can achieve the same result as before with a more concise query:

SELECT
  Product,
  January,
  February,
  March
FROM sales
PIVOT (
  SUM(Sales) FOR Month IN ('January', 'February', 'March')
) AS P;

Output:

In this method, we directly use the PIVOT clause, which takes care of the underlying logic of the CASE statement, making the query more compact and easier to maintain.

Conclusion:

PIVOT is a powerful feature in MySQL that allows users to transform data from rows to columns, facilitating better data analysis and reporting. In this blog, we explored two methods to accomplish the PIVOT operation: using CASE statements and the PIVOT clause in MySQL 8.0+. We witnessed how the latter method significantly simplifies the query, making it more elegant and easier to comprehend.