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:

References

https://learn.microsoft.com/en-us/sql/t-sql/queries/from-using-pivot-and-unpivot?view=sql-server-ver16