Using PIVOT to Flip Data from Wide to Tall

Question

I have a table that is rather wide that I would like to convert to tall. The data currently resides like this:

VEND   YEAR   I1_DOLS   I1_QTY   I2_DOLS   I2_QTY   I3_DOLS   I3_QTY ...
1234   2011   101587    508      203345    334      105938    257
1234   2012   257843    587      235883    247      178475    456
1011   2010   584737    432      587274    356      175737    563
1011   2011   517774    356      483858    456      481785    354

I would like to convert this to a table that looks like this:

VEND   YEAR   MONTH   DOLS     QTY
1234   2011   1       101587   508
1234   2011   2       203345   334
1234   2011   3       105938   257
1234   2012   1       257843   587
1234   2012   2       235883   247
.
.
.

I assume that a PIVOT is what I need, but I can't seem to figure this out.

Solution

You can unpivot the data using CROSS APPLY (VALUES). Here is an article to explain how this is done:

http://www.sqlservercentral.com/articles/CROSS+APPLY+VALUES+UNPIVOT/91234/

Basically the code is:

SELECT vend,
  year,
  month,
  dols, 
  qty
FROM YourTable t
CROSS APPLY 
(
    VALUES
        (1, I1_DOLS, I1_QTY),
        (2, I2_DOLS, I2_QTY),
        (3, I3_DOLS, I3_QTY)
) x (month, dols, qty);

See SQL Fiddle with Demo

Or you could use a UNION ALL query:

select vend, year, 1 month, [I1_DOLS] Dols, [I1_QTY] Qty
from yourtable
union all
select vend, year, 2 month, [I2_DOLS] Dols, [I2_QTY] Qty
from yourtable
union all
select vend, year, 3 month, [I3_DOLS] Dols, [I3_QTY] Qty
from yourtable

See SQL Fiddle with Demo

Or you can even apply both the UNPIVOT and the PIVOT function to transform the data:

select *
from
(
  select vend,
    year,
    replace(replace(replace(col, 'I', ''), '_Dols', ''), '_Qty', '') month,
    case when col like '%Dols%' then 'dols' else 'qty' end col_name,
    value
  from 
  (
    select vend, year, [I1_DOLS], [I1_QTY], [I2_DOLS], [I2_QTY], [I3_DOLS], [I3_QTY]
    from yourtable
  ) src
  unpivot
  (
    value
    for col in ([I1_DOLS], [I1_QTY], [I2_DOLS], [I2_QTY], [I3_DOLS], [I3_QTY])
  ) un
) unp
pivot
(
  max(value)
  for col_name in (dols, qty)
) piv

See SQL Fiddle with Demo.

All three will give the same result:

| VEND | YEAR | MONTH |   DOLS | QTY |
--------------------------------------
| 1234 | 2011 |     1 | 101587 | 508 |
| 1234 | 2011 |     2 | 203345 | 334 |
| 1234 | 2011 |     3 | 105938 | 257 |
| 1234 | 2012 |     1 | 257843 | 587 |
| 1234 | 2012 |     2 | 235883 | 247 |
| 1234 | 2012 |     3 | 178475 | 456 |
| 1011 | 2010 |     1 | 584737 | 432 |
| 1011 | 2010 |     2 | 587274 | 356 |
| 1011 | 2010 |     3 | 175737 | 563 |
| 1011 | 2011 |     1 | 517774 | 356 |
| 1011 | 2011 |     2 | 483858 | 456 |
| 1011 | 2011 |     3 | 481785 | 354 |

Comments

[Andriy M]

Your CROSS APPLY suggestion is just fantastic! It seems so simple, and I can't believe I've never thought of that technique.

[Taryn]

@AndriyM Thanks, I learned the technique from Cade Roux's answer here -- stackoverflow.com/questions/13591818/…

[Andriy M]

Hey, I've already seen (and voted) that! What a forgetful person I am... But thank you all the same :)