1

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.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Aivan Monceller
  • 4,636
  • 10
  • 42
  • 69
  • 1
    This answer can show you some methods in SQL server: http://stackoverflow.com/questions/2113251/tsql-help-with-unpivot – Tim Lehner Jan 16 '12 at 19:06

1 Answers1

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
mwigdahl
  • 16,268
  • 7
  • 50
  • 64