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?

Possible duplicate of [SQL Server dynamic PIVOT query?](https://stackoverflow.com/questions/10404348/sql-server-dynamic-pivot-query) — Thom A, Feb 08 '18 at 12:26

Gordon Linoff · Answer 1 · 2018-02-08T13:25:09.117

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;

edited Feb 08 '18 at 13:25

answered Feb 08 '18 at 12:30

Gordon Linoff

1,242,037
58
646
786

Yes sir, such pain. Thanks though, but is there a way to do it dynamically? – Chendur Feb 08 '18 at 12:36
@Chendur if there's a way to do it statically, there's a way to do it dynamically. You just need to make full use of T-SQL! Happy coding! – John Bell Feb 08 '18 at 13:05
@Chendur . . . Copying lines for 12 months seems much simpler to me than a dynamic expression. – Gordon Linoff Feb 08 '18 at 13:25
@JohnBell Totaly, but any leads would be much helpful – Chendur Feb 08 '18 at 13:38
@Chendur . . . More specifically, this answers the question that you have asked. If you want a dynamic version, then ask another question. – Gordon Linoff Feb 08 '18 at 13:39
@GordonLinoff The actual table has 200 odd columns :( Example posted here was to portray my query in a simple way – Chendur Feb 08 '18 at 13:40
@Chendur . . . As I say, ask another question. – Gordon Linoff Feb 08 '18 at 13:43

SQL - Interchange rows and columns

1 Answers1