SQL Server 取消数据透视表，其中列标题作为元组

Question

如何透视下表。表格格式如下：

结果图像

在取消透视查询之后，即

select 
    u.parentcustomerid, u.Parentcustomer, u.Billed_Rate 
from 
    #tbl_ONOFFPVHZRate s
unpivot 
    (Billed_Rate for details in ([PV ON rate],[PV Off rate],[HZ ON rate],[HZ Off rate])) u

结果图像

parentcustomerid    Parentcustomer  Billed_Rate PV/HZ/onOFF

---

1                        ACI    346.0314334 PV on rate
1                        ACI    217.967008  PV off Rate
1                        ACI    0            HZ on Rate
1                        ACI    187.1165653 HZ off Rate

Answer 1

在交叉申请的帮助下

Declare @YourTable table (parentCustomerid int,parentcustomer varchar(50),[PV On rate] decimal(18,13),[PV Off rate] decimal(18,13),[Hz On Rate] decimal(18,13),[Hz Off Rate] decimal(18,13))
Insert into @YourTable values
(1,'ACI',346.031433420653,217.967008048837,0,187.116565263531),
(2,'ADP',639.236987707151, 44.174136118757,379.378754724713,76.539884754392)
Select parentCustomerid
      ,parentcustomer
      ,B.*
 From  @YourTable A
 Cross Apply ( values ([PV On rate] ,'PV On rate')
                     ,([PV Off rate],'PV Off rate')
                     ,([Hz On Rate] ,'Hz On Rate')
                     ,([Hz Off Rate] ,'Hz Off Rate')
             ) B ([Billed_Rate],[PV/HZ/onOFF])

返回

parentCustomerid    parentcustomer  Billed_Rate         PV/HZ/onOFF
1                   ACI             346.0314334206530   PV On rate
1                   ACI             217.9670080488370   PV Off rate
1                   ACI             0.0000000000000     Hz On Rate
1                   ACI             187.1165652635310   Hz Off Rate
2                   ADP             639.2369877071510   PV On rate
2                   ADP             44.1741361187570    PV Off rate
2                   ADP             379.3787547247130   Hz On Rate
2                   ADP             76.5398847543920    Hz Off Rate