SELECT statement to convert columns into rows

Question

We have a result set that has one row with four columns:

value1, value2, value3, value4

How can we turn it into a result set that has four rows with one column:

value1
value2
value3
value4

Answer 1

As long as you are using SQL 2005 or later you can use UNPIVOT

SELECT value
FROM (SELECT a, b, c, d FROM myTable) as x
UNPIVOT (value FOR vx IN (a,b,c,d)) as p 

See also Using PIVOT and UNPIVOT on TechNet