UNPIVOT Examples in SQL Server

Using the UNPIVOT operator (introduced in SQL Server 2005), you can reverse PIVOT operation by rotating columns into rows.

First, we will create a table and insert some test data:

CREATE TABLE SalesTest2 (Name nvarchar(50), Jan int, Feb int, Mar int, Apr int)
GO
INSERT INTO SalesTest2 VALUES ('John', 129, 45, 37, 77)
INSERT INTO SalesTest2 VALUES ('Mary', 99, 75, 55, 61)
INSERT INTO SalesTest2 VALUES ('Amy', 43, 84, 60, 25)
INSERT INTO SalesTest2 VALUES ('Paul', 62, 44, 130, 71)
GO

This is what the data in the table looks like:

SELECT *
FROM SalesTest2

unpivot_example_table_select

If you want to list sale values for each sales person for each month, ordered by sales person name, use the following code:

SELECT Name, Mnt, SaleValue
FROM
(SELECT *
FROM SalesTest2)AS st2
UNPIVOT
(SaleValue FOR Mnt IN (Jan, Feb, Mar, Apr)) AS ListSales
ORDER BY Name

unpivot_example_table_order_by_name

If you want to list sale values for each sales person for each month, ordered by sale value, use the following code:

SELECT Name, Mnt, SaleValue
FROM
(SELECT *
FROM SalesTest2)AS st2
UNPIVOT
(SaleValue FOR Mnt IN (Jan, Feb, Mar, Apr)) AS ListSales
ORDER BY SaleValue

unpivot_example_table_order_by_salevalue

Related blog post:
PIVOT Examples in SQL Server