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

i've got 1 row with many c开发者_运维百科olumns:

col1|col2|col3|...

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

col1

col2

col3

..

---

UNPIVOT if you're using version 2005+

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

---

If SQL Server 2000 or lower...

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

---

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