PIVOT and UNPIVOT are Transact-SQL operators which are used for transforming rows to columns and vice versa. These operators were added to SQL Server from 2005 version. Before that there was only one approach how to turn data. It was complex series of SELECT...CASE statements.
PIVOT
PIVOT rotates a table-valued expression by turning the unique values from one column into multiple columns in the output. Also PIVOT runs aggregations on any remaining column values that should be appeared in the final output. PIVOT is useful for visualizing data by years, quarters, months, etc.
Let’s check the PIVOT syntax with a simple example. Below is a table with the earnings of employees from 2022 to 2023 by quarter.
CREATE TABLE Employees (
Employee_Id INT,
[Year] INT,
[Quarter] INT,
Income MONEY)
INSERT Employees VALUES(1, 2022, 1, 100000)
INSERT Employees VALUES(1, 2022, 2, 100000)
INSERT Employees VALUES(1, 2022, 3, 100000)
INSERT Employees VALUES(1, 2022, 4, 150000)
INSERT Employees VALUES(1, 2023, 1, 100000)
INSERT Employees VALUES(2, 2022, 2, 90000)
INSERT Employees VALUES(2, 2022, 3, 95000)
INSERT Employees VALUES(2, 2022, 4, 90000)
INSERT Employees VALUES(2, 2023, 1, 120000)
INSERT Employees VALUES(3, 2023, 1, 200000)
| Employee_Id | Year | Quarter | Income |
|---|---|---|---|
| 1 | 2022 | 1 | 100000,00 |
| 1 | 2022 | 2 | 100000,00 |
| 1 | 2022 | 3 | 100000,00 |
| 1 | 2022 | 4 | 150000,00 |
| 1 | 2023 | 1 | 100000,00 |
| 2 | 2022 | 2 | 90000,00 |
| 2 | 2022 | 3 | 95000,00 |
| 2 | 2022 | 4 | 90000,00 |
| 2 | 2023 | 1 | 120000,00 |
| 3 | 2023 | 1 | 200000,00 |
And now we would like to get only one row for each employee but with data for all years.
SELECT Employee_Id, [2022], [2023]
FROM
(
SELECT Employee_Id, [Year], Income FROM Employees
) AS source_query
PIVOT
(
SUM(Income) FOR [Year] IN ([2022], [2023])
) AS pivoted_table
| Employee_Id | 2022 | 2023 |
|---|---|---|
| 1 | 450000,00 | 100000,00 |
| 2 | 275000,00 | 120000,00 |
| 3 | NULL | 200000,00 |
As you can see, instead of 10 rows we got only three for each employee. PIVOT created two columns [2022] and [2023] from one column [Year] with the aggregated sum of earnings. Aggregation (SUM in the example above) is mandatory in PIVOT.
Also please notice if there is no data for some columns, SQL Server puts NULLs in them as it happened for employee 3 in 2022. But aggregate function inside PIVOT does not consider null values in the value column. As you can see, employee 2 does not have earnings for the first quarter of 2022 but the aggregate function calculated sum correctly.
UNPIVOT
UNPIVOT rotates a pivoted table back by transforming columns into rows. But it does not aggregate values or change them in any way. Also UNPIVOT does not show NULL values. They just disappear in the output.
Let’s take a look at our previous example and create a table from pivoted result set.
CREATE TABLE Pivoted_Employees(
Employee_Id INT,
[2022] MONEY,
[2023] MONEY
)
INSERT Pivoted_Employees VALUES(1, 450000, 100000)
INSERT Pivoted_Employees VALUES(2, 450000, 120000)
INSERT Pivoted_Employees VALUES(3, NULL, 200000)
And now we will try to turn year columns into rows.
select Employee_Id, [Year], Income
from (
select Employee_Id, [2022], [2023]
from Pivoted_Employees
) as pivoted_employees
unpivot(
Income for [Year] in ([2022], [2023])
) as unpivoted_employees
| Employee_Id | Year | Income |
|---|---|---|
| 1 | 2022 | 450000,00 |
| 1 | 2023 | 100000,00 |
| 2 | 2022 | 450000,00 |
| 2 | 2023 | 120000,00 |
| 3 | 2023 | 200000,00 |
Result set contains only 5 rows because UNPIVOT does not know that original table (before pivoting) had quarters.
In the conclusion, PIVOT and UNPIVOT operators are useful for transforming data when you know how many columns the result set should contain. If not, you need to use more complex structures.
Top comments (2)
Nice! Tested on SQLize.online
Thanks! Your link is quite interesting. So many variants of databases. On my computer it works not very fast but results look pretty good. I should admit, it is very useful tool!