Convert Row To Column In SQL Server 2005

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
(SELECT col1, col2, col3FROM @TABLE) p
(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]


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

%d bloggers like this: