使用PIVOT将数据从宽到高翻转 [英] Using PIVOT to Flip Data from Wide to Tall
问题描述
我有一张桌子很宽,我想将其转换成高桌子.数据当前位于以下位置:
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
.
.
.
我认为我需要的是PIVOT,但我似乎无法弄清楚.
I assume that a PIVOT is what I need, but I can't seem to figure this out.
推荐答案
您可以使用CROSS APPLY (VALUES)
取消数据透视.这是一篇文章,说明如何完成此操作:
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/
基本上,代码是:
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);
请参见带有演示的SQL提琴
或者您可以使用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
请参见带有演示的SQL小提琴
或者甚至可以同时应用UNPIVOT
和PIVOT
函数来转换数据:
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
请参见带有演示的SQL小提琴.
这三个都将给出相同的结果:
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 |
这篇关于使用PIVOT将数据从宽到高翻转的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!