The PIVOT and UNPIVOT operators in SQL Server are powerful tools that allow you to transform data between columns and rows. Here's a brief overview of how each operator works and how you can use them.
PIVOT Operator
The PIVOT operator is used to convert unique values from one column into multiple columns in the result set. This is often useful in a reporting scenario where you want to turn row-oriented data into a more readable and compact columnar format.
Syntax for PIVOT:
SELECT <non-pivoted column(s)>,
[<value1>], [<value2>], ...
FROM
(SELECT <non-pivoted column(s)>, <pivoted column>, <aggregate function> AS <value>
FROM <table>
WHERE <condition>
GROUP BY <non-pivoted column(s)>, <pivoted column>) AS SourceTable
PIVOT
(SUM(<value>) FOR <pivoted column> IN ([<value1>], [<value2>], ...)) AS PivotTable;
Example:
Suppose you have sales data like this:
| Year | Product | Amount |
|------|---------|--------|
| 2022 | A | 100 |
| 2022 | B | 150 |
| 2023 | A | 200 |
| 2023 | B | 250 |
You want to pivot this data to summarize the amount sold per product per year:
SELECT Year, [A], [B]
FROM
(SELECT Year, Product, Amount
FROM SalesData) AS SourceTable
PIVOT
(SUM(Amount) FOR Product IN ([A], [B])) AS PivotTable;
This will produce:
| Year | A | B |
|------|-----|-----|
| 2022 | 100 | 150 |
| 2023 | 200 | 250 |
UNPIVOT Operator
The UNPIVOT operator is the converse of PIVOT; it transforms columns into rows, which is useful for normalizing data or preparing it for certain types of analysis.
Syntax for UNPIVOT:
SELECT <non-pivoted column(s)>, <pivoted column>, <value>
FROM
(SELECT <columns>
FROM <table> WHERE <condition>) AS SourceTable
UNPIVOT
(<value> FOR <pivoted column> IN ([<column1>], [<column2>], ...)) AS UnpivotTable;
Example:
Using the previous pivoted table example, if you want to unpivot it back to a more normalized form:
| Year | A | B |
|------|-----|-----|
| 2022 | 100 | 150 |
| 2023 | 200 | 250 |
You can use UNPIVOT as follows:
SELECT Year, Product, Amount
FROM
(SELECT Year, [A], [B]
FROM PivotTable) AS SourceTable
UNPIVOT
(Amount FOR Product IN ([A], [B])) AS UnpivotTable;
This will produce:
| Year | Product | Amount |
|------|---------|--------|
| 2022 | A | 100 |
| 2022 | B | 150 |
| 2023 | A | 200 |
| 2023 | B | 250 |
Summary
- PIVOT is used to turn unique values from a column into multiple columns.
- UNPIVOT is used to turn multiple columns into rows.
These operators are especially useful in scenarios where you handle reporting, data analysis, or data transformation tasks, allowing for easier analysis and presentation of data.