UNPIVOT is the reverse operation of PIVOT, by rotating columns to rows.
Refer to this for the PIVOT tutorial.
Example 1
Execute the following script to create the pvt1 table. (Alternatively, you can save the pivot table in the Example 1 from the PIVOT tutorial to the database):
CREATE TABLE [pvt1](
[City] [varchar](6) NOT NULL,
[Chicago] [money] NULL,
[Houston] [money] NULL,
[Los Angeles] [money] NULL,
[New York] [money] NULL,
[Phoenix] [money] NULL,
[San Jose] [money] NULL
)
INSERT INTO [pvt1] ([City], [Chicago], [Houston], [Los Angeles], [New York], [Phoenix], [San Jose]) VALUES ('Amount', 1400.00, 1700.00, 100.00, 1200.00, 2000.00, 600.00)The pvt1 table looks like this:
Execute script below to UNPIVOT the pvt1 table:
SELECT City, Amount
FROM
(SELECT [Chicago],[Houston], [Los Angeles], [New York], [Phoenix], [San Jose]
FROM pvt1) p
UNPIVOT
(
Amount FOR [City] IN
([Chicago],[Houston], [Los Angeles], [New York], [Phoenix], [San Jose])
)AS unpvt1;The output will be:
Example 2
Execute the following script to create the pvt2 table. (Alternatively, you can save the pivot table in the Example 2 from the PIVOT tutorial to the database):
CREATE TABLE [pvt2]( [Year] [int] NULL, [Chicago] [money] NULL, [Houston] [money] NULL, [Los Angeles] [money] NULL, [New York] [money] NULL, [Phoenix] [money] NULL, [San Jose] [money] NULL ) INSERT INTO [pvt2] ([Year], [Chicago], [Houston], [Los Angeles], [New York], [Phoenix], [San Jose]) VALUES (2021, 900.00, 1700.00, NULL, 1000.00, 1300.00, 600.00) INSERT INTO [pvt2] ([Year], [Chicago], [Houston], [Los Angeles], [New York], [Phoenix], [San Jose]) VALUES (2022, 500.00, NULL, 100.00, 200.00, 700.00, NULL)
The pvt2 table looks like this:
Execute script below to UNPIVOT the pvt2 table:
SELECT [City],[Year], [Amount]
FROM
(SELECT [Year], [Chicago],[Houston], [Los Angeles], [New York], [Phoenix], [San Jose]
FROM pvt2) p
UNPIVOT
(
Amount FOR [City] IN
([Chicago],[Houston], [Los Angeles], [New York], [Phoenix], [San Jose])
)AS unpvt2
ORDER BY [City], [Year]The output will be:
