PIVOT - UNPIVOT operator

Explain PIVOT and UNPIVOT operators in SQL

PIVOT and UNPIVOT are relational operators which were introduced in SQL Server 2005. These operators allow easy transformation of table-valued expression into another table.

PIVOT allows you to rotate rows into columns during the execution of an SQL Query. It performs an aggregation, and merges possible multiple rows into a single row in the output.

UNPIVOT does almost the opposite of PIVOT operator by rotating columns into rows, but it is not exactly opposite. PIVOT merges multiple rows into single while UNPIVOT fails to reproduce the original table due to the merged rows. Null input values to UNPIVOT disappear in its output.

A simple example of PIVOT operator

Suppose we have a dbo.currency_rate table which consists of three columns - in_currency_id, mn_rate and dt_currency.

-- Create table
CREATE TABLE dbo.currency_rate(in_currency_id int, mn_rate money, dt_currency datetime)

-- Insert data in table
INSERT INTO dbo.currency_rate VALUES (1, 60.15, '2015-06-15 09:59:16.640')
INSERT INTO dbo.currency_rate VALUES (2, 35.57, '2015-06-15 09:59:16.640')
INSERT INTO dbo.currency_rate VALUES (3, 78.90, '2015-06-15 09:59:16.640')
INSERT INTO dbo.currency_rate VALUES (4, 87.15, '2015-06-15 09:59:16.640')
INSERT INTO dbo.currency_rate VALUES (1, 10.18, '2015-06-16 09:59:16.640')
INSERT INTO dbo.currency_rate VALUES (2, 11.25, '2015-06-16 09:59:16.640')
INSERT INTO dbo.currency_rate VALUES (3, 08.48, '2015-06-16 09:59:16.640')
INSERT INTO dbo.currency_rate VALUES (4, 18.31, '2015-06-16 09:59:16.640')

-- Select and confirm the data before any operation
SELECT * FROM dbo.currency_rate

in_currency_id mn_rate dt_currency
1 60.15 2015-06-15 09:59:16.640
2 35.57 2015-06-15 09:59:16.640
3 78.90 2015-06-15 09:59:16.640
4 87.15 2015-06-15 09:59:16.640
1 10.18 2015-06-16 09:59:16.640
2 11.25 2015-06-16 09:59:16.640
3 8.48 2015-06-16 09:59:16.640
4 18.31 2015-06-16 09:59:16.640


Use PIVOT operator to transform rows into columns:

-- Pivot table with two rows and five columns
SELECT *
FROM
[dbo].[currency_rate]
PIVOT
(
AVG(mn_rate)
FOR [in_currency_id] IN ([1], [2], [3], [4])
) AS pivot_table;


Output:

dt_currency 1 2 3 4
2015-06-15 09:59:16.640 60.15 35.57 78.90 87.15
2015-06-16 09:59:16.640 10.18 11.25 8.48 18.31


A simple example of UNPIVOT operator

In continuation with the PIVOT operator example, save the result of the PIVOT operator in a temporary table:

SELECT *
INTO #tmp_pivot_table
FROM
[dbo].[currency_rate]
PIVOT
(
AVG(mn_rate)
FOR [in_currency_id] IN ([1], [2], [3], [4])
) AS pivot_table;


Use UNPIVOT operator to transform rows into columns:

SELECT [dt_currency], [in_currency_id], [mn_rate]
FROM #tmp_pivot_table
UNPIVOT
(
mn_rate
FOR in_currency_id IN ([1], [2], [3], [4])
) AS unpivot_table

Output:

dt_currency in_currency_id mn_rate
2015-06-15 09:59:16.640 1 60.15
2015-06-15 09:59:16.640 2 35.57
2015-06-15 09:59:16.640 3 78.90
2015-06-15 09:59:16.640 4 87.15
2015-06-16 09:59:16.640 1 10.18
2015-06-16 09:59:16.640 2 11.25
2015-06-16 09:59:16.640 3 8.48
2015-06-16 09:59:16.640 4 18.31