Using PIVOT to Transfer Data from Wide Angle
I have a fairly large table that I would like to convert to tall. The data is currently stored as follows:
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 guess PIVOT is what I need, but I cannot figure it out.
source to share
You can disable data with CROSS APPLY (VALUES)
. Here's an article explaining how it's done:
http://www.sqlservercentral.com/articles/CROSS+APPLY+VALUES+UNPIVOT/91234/
Basically the code:
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);
Or you can use a query UNION ALL
:
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
Or you can even apply functions UNPIVOT
and PIVOT
to convert 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 get 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 |
source to share