PIVOT/UNPIVOT multiple rows and columns

Question

I have a table with the following (very simplified) data:

ID   Value1   Value2   Value3
-----------------------------
1    5        7        Test
2    4        7        Test2

I want to get each column name and the value in the row for that column; so, my desired output would look like this:

Column        1        2
----------------------------
Value1        5        4
Value2        7        7
Value3        Test     Test2

I've been playing with PIVOT and UNPIVOT but can't quite figure it out. Any help would be appreciated.

SQLFiddle here.

@vkp: Assume the three listed. Once I have a simple example working, I can expand it using dynamic sql for an arbitrary number of columns. — zimdanen, Oct 13 '15 at 20:56

score 2 · Accepted Answer · answered Oct 13 '15 at 20:58

2

For me, what comes to mind is a union all with conditional aggregation:

select 'Value1',
       max(case when id = 1 then value1 end) as [1],
       max(case when id = 2 then value1 end) as [2]
from t
union all
select 'Value2',
       max(case when id = 1 then value2 end) as [1],
       max(case when id = 2 then value2 end) as [2]
from t
select 'Value3',
       max(case when id = 1 then value3 end) as [1],
       max(case when id = 2 then value3 end) as [2]
from t;

I think you can do it with pivot/unpivot, but I think this is simpler.

answered Oct 13 '15 at 20:58

Gordon Linoff

1,242,037
58
646
786

i was thinking the same..but assumed the `id` column could potentially have many values, in which case, this would be a bit cumbersome – Vamsi Prabhala Oct 13 '15 at 20:59
Thank you! This is what I need. – zimdanen Oct 13 '15 at 21:03

PIVOT/UNPIVOT multiple rows and columns

1 Answers1

Linked