交叉表实现PIVOT方式

   set nocount on
      
   declare  @Sdt1 varchar(30),
            @Sdt2 varchar(30)            

   select @Sdt1 = '2017-09-01', @Sdt2 = '2017-09-20'
   if isdate(@Sdt1) <> 1 or isdate(@Sdt2) <> 1
      or @Sdt1 > @Sdt2
   begin           
     Select '日期范围有问题' as [消息]          
     Return
   end;
               
   Create Table #Day(
        id int  identity(1,1) not null,
        sDt varchar(10) primary key
        )
        
   declare @TmpDt datetime, @i int
   select @tmpDt = convert(datetime, @Sdt1, 120), @i = 1;
   while @tmpDt <= convert(datetime, @Sdt2, 120)
   begin 
     insert into #Day(sDt)
       Select convert(varchar(10), @TmpDt, 120)
     Set @tmpDt = @tmpDt + 1
   end;     
   
   with Cte_BAmt
   as(
   select T.Sdt, V.OutCardTp, isnull(V.Amt, 0) Amt
     from #Day  T
     left join V_ChargeInfo V on T.SDt = convert(varchar(10),V.OutDt, 120)
    ),
  Cte_BNum
   as(
   select T.Sdt, V.OutCardTp, V.ID
     from #Day  T
     left join V_ChargeInfo V on T.SDt = convert(varchar(10),V.OutDt, 120)
    ),
  Cet_S1
    as(          
   select SDt, [1] as [月卡A金额],[9] as [临卡A金额],[13] as [免费卡A金额] 
   from Cte_BAmt as R
    pivot (sum(R.Amt)
       for R.OutCardTp
        in ([1],[9],[13])        
        ) AS Pvt_S1
       )
       ,
  Cet_SSum
    as(          
       select SDt, sum(isnull(Amt, 0)) as SSum
        from Cte_BAmt
       group by Sdt    
      )     
      ,      
   Cet_CSum
     as(          
       select SDt, count(1) as CSum
           from Cte_BNum
          group by Sdt    
      ),
                  
    Cet_C1
    as(              
    select SDt, [1] as [月卡A数量],[9] as [临卡A数量],[13] as [免费卡A数量] 
   from Cte_BNum as R
    pivot (Count(R.ID)
       for R.OutCardTp
        in ([1],[9],[13])                
      ) AS Pvt_C1   
     )
     
    select S.Sdt, [月卡A金额], [临卡A金额],[免费卡A金额],    
        [月卡A数量],[临卡A数量], [免费卡A数量],
        SS.SSum [金额合计], CS.CSum [数量合计]
      from Cet_S1 S
          inner join Cet_C1 C on S.Sdt = C.Sdt    
          inner join Cet_SSum SS on S.Sdt = SS.Sdt    
          inner join Cet_CSum CS on S.Sdt = CS.Sdt    
          
    --select  * from Cet_S1 
                    
   truncate table #Day                     
   drop table #Day  
     
   set nocount off