Switching 1 row with few columns into 1 column with few rows in MS SQL SERVER

Question

hi, i've got 1 row with many columns:

col1|col2|col3|...

and i want to have 1 column with many rows, like that:

col1
col2
col3
..

Answer 1

UNPIVOT if you're using version 2005+

http://www.tsqltutorials.com/unpivot.php

Answer 2

If SQL Server 2000 or lower...

How to rotate a table in SQL Server: http://support.microsoft.com/kb/175574

Answer 3

Take a look at UNPIVOT:

CREATE TABLE Table1 (col1 INT, col2 INT, col3 INT, col4 INT);
INSERT INTO Table1 (col1, col2, col3, col4) VALUES (1,2,3,4);

SELECT col, value FROM Table1
UNPIVOT (value FOR col IN (col1, col2, col3, col4)) AS unpvt

Result:

col  value
col1 1
col2 2
col3 3
col4 4

If you don't want to know which value came from which column, only select value:

SELECT value FROM Table1
UNPIVOT (value FOR col IN (col1, col2, col3, col4)) AS unpvt

Answer 4

Mind that if all the columns are not the same data type, you'll need to CAST/CONVERT before unpivoting the data. This is safe on SQL Server (as well as other databases):

SELECT x.col1
  FROM TABLE x
UNION ALL
SELECT y.col2
  FROM TABLE y    
UNION ALL
SELECT z.col3
  FROM TABLE z

The query will allow duplicates - change UNION ALL to UNION. It won't be as faster, because of the duplicate removal.

If SQL Server 2005+, you can use the UNPIVOT function:

 SELECT value
   FROM TABLE
UNPIVOT (value FOR col IN (col1, col2, col3))

Dynamic SQL is the only option if you have a variable number of columns, or more columns than you wish to construct the query for.