How to use the PIVOT function in Oracle SQL
Suppose you have a table called "sales" that looks like this
+-------+------+-------+-------+
| month | year | sales | region|
+-------+------+-------+-------+
| 1 | 2021 | 100 | west |
| 2 | 2021 | 200 | west |
| 3 | 2021 | 300 | west |
| 1 | 2021 | 150 | east |
| 2 | 2021 | 250 | east |
| 3 | 2021 | 350 | east |
+-------+------+-------+-------+
And you want to pivot the data to show the sales by region for each month of the year. You can use the PIVOT function to achieve this as follows:
SELECT * FROM
(
SELECT month, year, sales, region
FROM sales
)
PIVOT
(
SUM(sales)
FOR region IN ('west' as west_sales, 'east' as east_sales)
)
ORDER BY month, year;
The above query will return the following result:
+-------+------+-------------+-------------+
| month | year | west_sales | east_sales |
+-------+------+-------------+-------------+
| 1 | 2021 | 100 | 150 |
| 2 | 2021 | 200 | 250 |
| 3 | 2021 | 300 | 350 |
+-------+------+-------------+-------------+
In this example, the PIVOT function is used to transform the data from rows to columns based on the values in the "region" column. The function SUM(sales) is used to calculate the total sales for each region in each month. The resulting columns are named "west_sales" and "east_sales" using the "FOR region IN" clause.