PIVOT and UNPIVOT in SQL Server

There are several ways to transpose a dataset from rows to columns and columns to rows. SQL Pivot is one of the techniques that allows transposing of rows to columns and performs possible aggregations along the way. SQL PIVOT and SQL UNPIVOT relational operators transpose a table-valued two-dimensional data into another form of data. 

SQL PIVOT transposes a table-valued expression from a unique set of values from one column into multiple columns in the output and performs aggregations. SQL UNPIVOT performs the opposite operation of SQL PIVOT by transforming the columns of a table-valued expression into column values.

PIVOT relational operator converts data from row level to column level.

UNPIVOT relational operator is reverse process of PIVOT relational operator. UNPIVOT relational operator convert data from column level to row level.

Let's see the below example to understand.

Example:

PIVOT

Below is a simple example which shows average item's price of each item per weekday.

First, suppose we have a table which keeps daily records of all items' prices.

CREATE TABLE tbl_stock(item NVARCHAR(10), weekday NVARCHAR(10), price INT);

insert values into the table:

INSERT INTO tbl_stock VALUES
('Item1', 'Mon', 110), ('Item2', 'Mon', 230), ('Item3', 'Mon', 150),
('Item1', 'Tue', 115), ('Item2', 'Tue', 231), ('Item3', 'Tue', 162),
('Item1', 'Wed', 110), ('Item2', 'Wed', 240), ('Item3', 'Wed', 162),
('Item1', 'Thu', 109), ('Item2', 'Thu', 228), ('Item3', 'Thu', 145),
('Item1', 'Fri', 120), ('Item2', 'Fri', 210), ('Item3', 'Fri', 125),
('Item1', 'Mon', 122), ('Item2', 'Mon', 225), ('Item3', 'Mon', 140),
('Item1', 'Tue', 110), ('Item2', 'Tue', 235), ('Item3', 'Tue', 154),
('Item1', 'Wed', 125), ('Item2', 'Wed', 220), ('Item3', 'Wed', 142);

In order to perform aggregation which is to find the average price per item for each week day, we are going to use the relational operator PIVOT to rotate the column weekday of table-valued expression into aggregated row values as below:

SELECT * FROM tbl_stock
PIVOT (
AVG(price) FOR weekday IN ([Mon], [Tue], [Wed], [Thu], [Fri])
) pvt;

Output:

item	Mon	Tue	Wed	Thu	Fri
Item1	116	112	117	109	120
Item2	227	233	230	228	210
Item3	145	158	152	145	125

UNPIVOT

Lastly, in order to perform the reverse operation of PIVOT, we can use the relational operator UNPIVOT to rotate

columns into rows as below:

SELECT * FROM tbl_stock
PIVOT (
AVG(price) FOR weekday IN ([Mon], [Tue], [Wed], [Thu], [Fri])
) pvt
UNPIVOT (
price FOR weekday IN ([Mon], [Tue], [Wed], [Thu], [Fri])
) unpvt;

Output:

item	price	weekday
Item1	116	Mon
Item1	112	Tue
Item1	117	Wed
Item1	109	Thu
Item1	120	Fri
Item2	227	Mon
Item2	233	Tue
Item2	230	Wed
Item2	228	Thu
Item2	210	Fri
Item3	145	Mon
Item3	158	Tue
Item3	152	Wed
Item3	145	Thu
Item3	125	Fri