Archive for April, 2009

Convert Row To Column In SQL Server 2005

April 2, 2009

Let us suppose that you have a table like below

col1 | col2 | col3

——–*———*———-

Value 1 | Value 2 | Value 3

And change it to one that looks like this:

Name | Value

—–*———

col1 | Value 1

—–*———

col2 | Value 2

—–*———

col3 | Value 3

DECLARE @Table Table
(
col1 varchar(10),
col2 varchar(10),
col3 varchar(10)
)
INSERT INTO @TABLE VALUES (‘Value 1’, ‘Value 2’, ‘Value 3’)
INSERT INTO @TABLE VALUES (‘Value 4’, ‘Value 5’, ‘Value 6’)
INSERT INTO @TABLE VALUES (‘Value 7’, ‘Value 8’, ‘Value 9’)

SELECT col, colval
FROM
(SELECT col1, col2, col3FROM @TABLE) p
UNPIVOT
(ColVal FOR Col IN
(col1, col2, col3)
) AS unpvt

[Note: To execute above sql query, one important thing you have to do in your database i.e you have to execute
EXEC sp_dbcmptlevel databse_name, 90; — for sql server 2005
EXEC sp_dbcmptlevel qdevdb, 80; –for sql server 2000]

Advertisements

LinQ Best Book

April 2, 2009

linqbook