Pivoting Data with CASE in MySQL: A Practical Example

As a MySQL database expert, you’re well-versed in the intricacies of data manipulation and the power of SQL queries to extract meaningful insights from relational databases. While MySQL doesn’t have a built-in pivot function, you can leverage the CASE statement to effectively pivot data, transforming it into a summarized and aggregated format for enhanced analysis.

Let’s consider a scenario where you have a table named sales that stores sales data for different products, regions, and months. The table structure is as follows:

SQL

CREATE TABLE sales (
  product_id INT,
  region_id INT,
  sales_month INT,
  sales_amount DECIMAL(10,2)
);

To pivot this data by region and sales month, you can use the following SQL query:

SQL

SELECT
  region_id,
  sales_month,
  SUM(sales_amount) AS total_sales
FROM sales
GROUP BY region_id, sales_month
ORDER BY region_id, sales_month;

This query will group the sales data by region and sales month, and then calculate the total sales for each combination. The result will be a new table with the following structure:

SQL

region_id | sales_month | total_sales
---------+-------------+-------------
1        | 1          | 1000.00
1        | 2          | 1500.00
1        | 3          | 2000.00
2        | 1          | 800.00
2        | 2          | 1200.00
2        | 3          | 1600.00

However, if you want to pivot the data further to display the total sales for each region across all months, you can use the CASE statement to dynamically generate column names based on the sales month. Here’s the modified query:

SQL

SELECT
  region_id,
  SUM(CASE
    WHEN sales_month = 1 THEN sales_amount
    WHEN sales_month = 2 THEN sales_amount
    WHEN sales_month = 3 THEN sales_amount
    ELSE 0
  END) AS sales_month_1,
  SUM(CASE
    WHEN sales_month = 1 THEN sales_amount
    WHEN sales_month = 2 THEN 0
    WHEN sales_month = 3 THEN 0
    ELSE 0
  END) AS sales_month_2,
  SUM(CASE
    WHEN sales_month = 1 THEN 0
    WHEN sales_month = 2 THEN sales_amount
    WHEN sales_month = 3 THEN 0
    ELSE 0
  END) AS sales_month_3
FROM sales
GROUP BY region_id
ORDER BY region_id;

This query uses the CASE statement to create separate columns for each sales month (sales_month_1, sales_month_2, sales_month_3) and dynamically calculates the total sales for each month. The result will be a new table with the following structure:

SQL

region_id | sales_month_1 | sales_month_2 | sales_month_3
---------+-------------+-------------+-------------
1        | 1000.00      | 1500.00      | 2000.00
2        | 800.00       | 1200.00      | 1600.00

This example demonstrates how you can effectively pivot data in MySQL using the CASE statement, enabling you to transform raw data into a more structured and analyzable format for informed decision-making.