pivot 使用

if object_id('tempdb..#temp1') is not null drop table #temp1  

if object_id('tempdb..#temp2') is not null drop table #temp2   

 select a.ClientNo, a.YYMonth AS '年月',a.DeptNo AS '部門代碼',a.AccCode as '會計科目',    b.DescCh AS '費用來源',d.DescCh AS '費用屬性2',c.DescCh AS 'GroupA',   a.AmountS AS '總金額',a.ShareLogic AS '分攤邏輯',a.ProductType AS '生產類型',a.Customer+'_'+a.Product+'_'+a.ProductSite+'_'+a.BelongSite as Cust_Prod,a.Amount  

 into #temp1   

from CPBResult a    

INNER JOIN  dbo.BASPubcode b ON b.ClientNo=@ClientNo AND  a.ExpenseType=b.CodeNo  AND b.CodeType='E001'    

INNER JOIN dbo.BASPubcode c ON c.ClientNo=@ClientNo AND a.GroupA=c.CodeNo AND c.CodeType='AC01'    

INNER JOIN dbo.BASPubcode d ON d.ClientNo=@ClientNo AND a.ExpenseType2=d.CodeNo AND d.CodeType='HC02'    

where a.ClientNo=@ClientNo and a.YYMonth like @YYMonth+'%' AND a.ShareLogic LIKE @ShareLogic+'%'    

--SELECT * FROM dbo.CPBResult   

 --select * from #temp1    

select a.Customer+'_'+a.Product+'_'+a.ProductSite+'_'+a.BelongSite as Cust_Prod  into #temp2    from dbo.SCCustomer a WHERE ClientNo=@ClientNo AND YYMonth=@YYMonth    order by Sortby

   declare @sql1 varchar(MAX)        

select  @sql1 = isnull(@sql1 + '],[' , '') +  Cust_Prod from #temp2      

set @sql1 = '[' + @sql1 + ']'          

exec ('select *  from #temp1      pivot (      Min(Amount)      for Cust_Prod      in (' + @sql1 +')      ) a' )