动态和多列上的动态透视

Question

我正在尝试对SQL Server 2012中的多个列执行动态透视

我的表格如下

customer UnitNo  invoiceNo  invDate    heads   Amt periodFrmDT  periodToDT  
abc      GF-3    C0000001  2015-11-01 Charge1  100 2015-11-01   2015-11-30  
abc      GF-3    C0000001  2015-11-01 Charge2  500 2015-11-10   2015-12-10  
abc      GF-3    C0000001  2015-11-01 charge3  600 2015-10-01   2015-10-30  

我想要下面这样的结果

customer unitNo invoiceNo   invDate    Charge1  PeriodFrmDT periodToDT  Charge2  PeriodFrmDT    periodToDT  Charge3  PeriodFrmDT periodToDT

abc      GF-3   C0000001    2015-11-01  100     2015-11-01  2015-11-30  500      2015-11-10   2015-12-10    600     2015-10-01   2015-10-30

我已经试过了

 select * from (
    select c.Customer,unitNo, ih.invoiceNo
    ,bh.heads,it.Amt,periodFrmDT,PeriodToDT
    from invHeader ih
                inner join customer c on c.custID =ih.custID
                inner join mstUnit mu on mu.unitID=ih.unitID
                inner join invTran it on it.invID=ih.invHeaderID
                inner join billingHeads bh on bh.BillHeadID=it.headID           
    )P 
    Pivot 
    (
     max(amt)         
     for Head in([charge1],[charge2],[charge3])      
    ) as pvt

请帮帮我..

Answer 1

这将为您提供您指定的表。这里的关键是，您必须首先取消透视每个费用的金额、开始日期和结束日期。然后，这允许您重新旋转并查看所有3个值。

首先，我伪造了你的数据如下：

declare @cte table (customer nvarchar(4)
        , UnitNo nvarchar(4)
        , invoiceNo nvarchar(8)
        , invDate datetime
        , heads nvarchar(8)
        , Amt int 
        , periodFrmDT datetime
        , periodToDT  datetime)
insert into @cte ( customer, UnitNo,  invoiceNo,  invDate,    heads,   Amt, periodFrmDT,  periodToDT)
values 
('abc',      'GF-3',    'C0000001',  '2015-11-01', 'Charge1',  100, '2015-11-01',   '2015-11-30'), 
('abc',      'GF-3',    'C0000001',  '2015-11-01', 'Charge2',  500, '2015-11-10',   '2015-12-10'),  
('abc',      'GF-3',    'C0000001',  '2015-11-01', 'charge3',  600, '2015-10-01',   '2015-10-30');

然后，我使用以下查询来获取您所请求的数据：

with mycte as (select * from @cte
               )
select * from 
    (
    select customer, UnitNo  ,invoiceNo  ,invDate, heads + '_' + columnname as heads, value 
    from (select customer, UnitNo, invoiceNo, invDate, heads
               , cast(Amt as nvarchar(10)) as Amt
               , left(convert(nvarchar(10), periodFrmDT, 120), 10) as periodFrm
               , left(convert(nvarchar(10), periodToDT, 120), 10)  as periodTo 
          from mycte
          ) as t1
    unpivot(value for columnname in (Amt, periodFrm ,periodTo)) as t2
    ) as t3 -- this table gives you your initial columns, then 2 more with the column headings for the next stage, and the values
            -- columnname contains [Charge1_Amt], [Charge1_periodFrm], [Charge1_periodTo] etc
pivot (min(value) for heads in ( [Charge1_Amt], [Charge1_periodFrm], [Charge1_periodTo]
                               , [Charge2_Amt], [Charge2_periodFrm], [Charge2_periodTo]
                               , [charge3_Amt] , [charge3_periodFrm], [charge3_periodTo]
                               )
       ) as t4 -- And then we can re-pivot it all back up again.

要直接从您的数据使用第二个查询，请替换

with mycte as (select * from @cte
               )

使用

with mycte as (
    -- This is the query with your base data
    select c.Customer,unitNo, ih.invoiceNo, invDate ,bh.heads,it.Amt,periodFrmDT,PeriodToDT
    from invHeader ih
                inner join customer c on c.custID =ih.custID
                inner join mstUnit mu on mu.unitID=ih.unitID
                inner join invTran it on it.invID=ih.invHeaderID
                inner join billingHeads bh on bh.BillHeadID=it.headID   
               )