Convert row with columns of data into multiple rows with key value columns

Question

I have a SELECT inside a stored procedures which outputs data into this format.

          A      B      C
rowkey    1      2      3

I need to transform it into something like this:

         key  value
rowkey   A    1
rowkey   B    2
rowkey   C    3

How should I go about transforming it into this?

I am not allowed to touch the SELECT statement, so I should find a way to transform it perhaps creating a temporary table.

This answer can show you some methods in SQL server: stackoverflow.com/questions/2113251/tsql-help-with-unpivot — Tim Lehner
– Tim Lehner, Commented Jan 16, 2012 at 19:06

mwigdahl · Accepted Answer · 2012-01-16 19:24:00Z

1

This ought to get you going if you're in SQL 2005 or higher...

You would store the result of your internal SELECT into a table variable, then transform it with something like the below:

DECLARE @test TABLE (rowkey int identity, A int, B int, C int)

INSERT INTO @test (A, B, C)
VALUES (1, 2, 3)

SELECT rowkey, [Key], [Value]
FROM
(SELECT rowkey, A, B, C
FROM @test) t
UNPIVOT
( [Value] FOR [Key]  IN
    (A, B, C)
) AS u

answered Jan 16, 2012 at 19:24

mwigdahl

16.6k7 gold badges52 silver badges64 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Convert row with columns of data into multiple rows with key value columns

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related