UNPIVOT SQL with columns查询

Question

我有Price桌

WhseKey ItemKey CurrID  Sheet1Price  Sheet2Price Sheet3Price   Sheet4Price
24      452     USD     14.90000     14.90000   13.70000       12.50000    11.03000
24      453     USD     1.15000      1.15000    1.05000        0.95000     0.85000
24      454     USD     12.95000     12.95000   11.90000       10.88000    9.70000  
24      459     USD     3.95000      3.95000    3.65000        3.30000     2.92000

我希望结果是这样的：

CurrID Name ID 
USD Sheet1Price Sheet1Price
USD Sheet2Price Sheet2Price
USD Sheet3Price Sheet3Price
USD Sheet4Price Sheet4Price

当前查询：

select UPV.CurrID , UPV.Name
from Price
unpivot
(
    Name
    for Price in (Sheet1Price, Sheet2Price, Sheet3Price, Sheet4Price)
) UPV;

但是结果是不正确的，它是这样的：

CurrID Name
USD 14.90000
USD 13.70000
USD 12.50000
USD 11.03000
USD 1.15000
USD 1.05000
USD 0.95000
USD 0.85000
USD 12.95000
USD 11.90000

我错过了什么？

Answer 1

要输出您应该使用UPV.Price的文件名，请尝试执行以下操作：

select WhseKey, ItemKey, UPV.CurrID , UPV.Name, UPV.Price
from Price
unpivot
(
    Price
    for Name in (Sheet1Price, Sheet2Price, Sheet3Price, Sheet4Price)
) UPV;