The PIVOT operator in SQL is used to transform or "pivot" rows of data into columns, enabling you to summarize and analyze data more effectively. It’s especially useful for creating cross-tabulations of data. Below is a general overview of how to use the PIVOT operator with an example.
Syntax
The basic syntax for the PIVOT operator is as follows:
SELECT <non-pivoted column>, <pivoted column1>, <pivoted column2>, ...
FROM
(
SELECT <column1>, <column2>, <column3>
FROM <table>
) AS SourceTable
PIVOT
(
<aggregate function>(<column to aggregate>)
FOR <column to pivot> IN (<value1>, <value2>, ... )
) AS PivotTable;
Example
Suppose you have the following table called Sales:
| Product | Year | Amount |
|---------|------|--------|
| A | 2021 | 100 |
| A | 2022 | 150 |
| B | 2021 | 200 |
| B | 2022 | 250 |
You want to pivot this data so that the years become columns and the amounts are aggregated. The resulting table should look like:
| Product | 2021 | 2022 |
|---------|------|------|
| A | 100 | 150 |
| B | 200 | 250 |
SQL Query using PIVOT
Here’s how you would implement this using the PIVOT operator in SQL:
SELECT Product, [2021], [2022]
FROM
(
SELECT Product, Year, Amount
FROM Sales
) AS SourceTable
PIVOT
(
SUM(Amount) -- You can also use other aggregate functions like AVG, COUNT, etc.
FOR Year IN ([2021], [2022])
) AS PivotTable;
Explanation
1. Inner Query: The inner SELECT statement retrieves the baseline data from the Sales table.
2. PIVOT Clause:
- SUM(Amount) specifies that you want to aggregate the amounts.
- FOR Year IN ([2021], [2022]) indicates you want to create columns for the years 2021 and 2022.
3. Final SELECT Statement: The outer SELECT retrieves the pivoted result.
Notes
- Ensure that the values used in the IN list of the PIVOT clause are valid values from the column you are pivoting.
- You can also pivot on multiple columns or use different aggregate functions as needed.
- Not all SQL databases support the PIVOT operator. For example, it's available in Microsoft SQL Server but not in MySQL.
This example gives a clear illustration of how to use the PIVOT operator to rearrange data, making it easier to analyze and present.