带列查询的UNPIVOT SQL

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;