DEV Community

Olesia Dudareva
Olesia Dudareva

Posted on • Updated on

How to rotate data using Pivot & Unpivot operators

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)
Enter fullscreen mode Exit fullscreen mode
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
Enter fullscreen mode Exit fullscreen mode
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)
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode
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)

Collapse
 
rozhnev profile image
Slava Rozhnev

Nice! Tested on SQLize.online

Collapse
 
notte profile image
Olesia Dudareva

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!