Transpose Columns into Rows (UNPIVOT)

Issue:
How to Transpose (PIVOT (actually UNPIVOT ) / Transform) Columns into Rows
Sometimes you want to transpose Columns into Rows in SQL Server.

Solution:
The below T-SQL will transpose or transform Columns into Rows. It uses the reverse of PIVOT which is UNPIVOT.

DECLARE @Table Table
(NameCol1 varchar(10),
NameCol2 varchar(10),
NameCol3 varchar(10))
INSERT INTO @TABLE VALUES ('Name 1', 'Name 2', 'Name 3')
--INSERT INTO @TABLE VALUES ('Name 4', 'Name 5', 'Name 6')
--INSERT INTO @TABLE VALUES ('Name 7', 'Name 8', 'Name 9')

SELECT Name, Nameval
FROM
(SELECT NameCol1, NameCol2, NameCol3
FROM @TABLE) p
UNPIVOT
(NameVal FOR Name IN
(NameCol1, NameCol2, NameCol3)
)AS unpvt


-- OUTPUT
Name             Nameval
-------------     ----------
NameCol1      Name 1
NameCol2      Name 2
NameCol3      Name 3