SQL - Interchange rows and columns

Question

I have a case where I need to interchange the rows and columns. Yes , I tried with Pivot and UnPivot which doesnt give me the desired outcome.

My source table :

+-------+------+------+-----+-----+
| month | year | po1  | po2 | po3 |
+-------+------+------+-----+-----+
| Jan   | 2013 |  100 |  20 |  10 |
| Feb   | 2014 | 1100 | 120 | 110 |
+-------+------+------+-----+-----+

I was able to make a static pivot

select Description,value from #test
unpivot (value for Description in (year,po1, po2, po3)) unpiv;

This is the result I'm able to generate

+-------------+-------+
| Description | value |
+-------------+-------+
| year        |  2013 |
| po1         |   100 |
| po2         |    20 |
| po3         |    10 |
| year        | 12013 |
| po1         |  1100 |
| po2         |   120 |
| po3         |   110 |
+-------------+-------+

Expected output :

+------+------+------+
| Desc | Jan  | Feb  |
+------+------+------+
| year | 2013 | 2014 |
| po1  |  100 | 1100 |
| po2  |   20 |  120 |
| po3  |   10 |  110 |
+------+------+------+

Further, the rows / columns would keep changing, is there a way to make it dynamic?

Answer 1

This sort of transpose is a pain -- requiring pivoting and unpivoting. Here is one method:

select v.description,
       max(case when t.month = 'Jan' then v.value end) as Jan,
       max(case when t.month = 'Feb' then v.value end) as Feb
from #test t cross apply
     (values ('year', t.year), ('p01', p01), ('p02', p02), ('p03', p03)
     ) v(description, value) 
group by v.description;