CODEWITHSUNDEEP
sqlsql-serverstored-procedures
Aivan Monceller
Aivan Monceller

Reputation: 4670

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

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.

Upvotes: 1

Views: 677

Answers (1)

mwigdahl
mwigdahl

Reputation: 16578

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

Upvotes: 1

Related Questions