How to rotate data using Pivot & Unpivot operators
Tags: sql, database, dataengineering, mssql
Published: 2023-05-15 10:08:49

`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.

```sql
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.

```sql
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.

```sql
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.

```sql
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.