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
