What is the usage of the pivot function in MySQL?

Although there is no built-in PIVOT function in MySQL, you can achieve similar functionality using conditional aggregation and the CASE statement.

In MySQL, you can use conditional aggregation and the CASE statement to pivot rows into columns. Here is an example illustrating how to achieve the PIVOT functionality using conditional aggregation and the CASE statement.

Assume we have a table named sales, which includes the following data:

+-------+-------+-------+-------+
| Month | Sales | Region| Product|
+-------+-------+-------+-------+
|   1   |  100  |   A   |   X   |
|   1   |  200  |   B   |   X   |
|   2   |  150  |   A   |   Y   |
|   2   |  250  |   B   |   Y   |
+-------+-------+-------+-------+

We want to pivot the values in the Region column as columns and the values in the Sales column as the corresponding values, resulting in the following outcome:

+-------+-------+-------+
| Month |   A   |   B   |
+-------+-------+-------+
|   1   |  100  |  200  |
|   2   |  150  |  250  |
+-------+-------+-------+

The following query can achieve this purpose:

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

This query utilizes conditional aggregation and CASE statements.

1. For each unique Month value, add up the Sales values that meet the criteria using the SUM function, creating new columns A and B.
2. The CASE statement is used to return different results (Sales value or 0) based on conditions (Region value).

The outcome will be grouped based on the Month and new columns A and B will be created. Column A will contain Sales values for Region ‘A’, while column B will contain Sales values for Region ‘B’.