

create   PROCEDURE sp_GunlukAlisSatisPivot @departman varchar(max), @Tarih1 varchar(20), @Tarih2 varchar(20)  
as   
begin 
declare @tar1 varchar(8)
declare @tar2 varchar(8)

  set @tar1=convert(varchar(8),replace(@Tarih1,'.',''))
  set @tar2=convert(varchar(8),replace(@Tarih2,'.',''))

  DECLARE @PivotKolonlar VARCHAR(MAX)  
  SELECT @PivotKolonlar=    
    COALESCE (@PivotKolonlar +',['+CAST(BAACIKLA AS VARCHAR) +']',  
    '['+CAST(BAACIKLA AS VARCHAR)+']')  
    FROM (SELECT DISTINCT(BAACIKLA) FROM ACARI ) AS DISTBAACIKLA  
   
  DECLARE @PVT nvarchar(max)  
  
  declare @EkParam varchar(max)  
  set @EkParam=' where 1=1 '  
  if len(replace(@departman,' ',''))>0  
    set @EkParam = ' and C.Departman Like '''+@departman+''' '  
    
  if len(@Tarih1)+len(@Tarih2) >0  
  set @EkParam = @EkParam+ 'and TARIH between '''+@tar1 +''' and '''+@tar2+''' ';   
  
      
  set @PVT=N'select * from (  
    select   C.MUSKOD,A.MUSADI,C.Departman,C.BAACIKLA ,SUM(C.BORC-C.ALACAK) AS TUTAR,COUNT(C.MUSKOD) AS ISLEMSAYISI from ACARI AS C  
    JOIN AMUSTERI AS A ON A.MUSKOD=C.MUSKOD '+@EkParam+'  
    GROUP BY  C.MUSKOD,A.MUSADI,C.Departman,C.BAACIKLA) as PivotT  
    pivot ( Sum(TUTAR) for BAACIKLA in ('+@PivotKolonlar+') ) as pvt  
    '  
   EXECUTE (@PVT)   
end