Microsoft SQL Server PIVOT / UNPIVOT Simple PIVOT & UNPIVOT (T-SQL)
Example
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 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);
The table should look like below:
+========+=========+=======+
| item | weekday | price |
+========+=========+=======+
| Item1 | Mon | 110 |
+--------+---------+-------+
| Item2 | Mon | 230 |
+--------+---------+-------+
| Item3 | Mon | 150 |
+--------+---------+-------+
| Item1 | Tue | 115 |
+--------+---------+-------+
| Item2 | Tue | 231 |
+--------+---------+-------+
| Item3 | Tue | 162 |
+--------+---------+-------+
| . . . |
+--------+---------+-------+
| 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;
Result:
+--------+------+------+------+------+------+
| item | Mon | Tue | Wed | Thu | Fri |
+--------+------+------+------+------+------+
| Item1 | 116 | 112 | 117 | 109 | 120 |
| Item2 | 227 | 233 | 230 | 228 | 210 |
| Item3 | 145 | 158 | 152 | 145 | 125 |
+--------+------+------+------+------+------+
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;
Result:
+=======+========+=========+
| 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 |
+-------+--------+---------+
