CODEWITHSUNDEEP
sqlt-sql
clint
clint

Reputation: 21

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

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
..

Upvotes: 2

Views: 240

Answers (3)

Mark Byers
Mark Byers

Reputation: 837946

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

Upvotes: 0

David
David

Reputation: 73554

If SQL Server 2000 or lower...

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

Upvotes: 1

Joe Phillips
Joe Phillips

Reputation: 51100

UNPIVOT if you're using version 2005+

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

Upvotes: 1

Related Questions