-- sp_CariPivot '',''

alter  PROCEDURE sp_CariPivot @carikodlar varchar(max),@saticilar varchar(max)
as begin


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=''
if len(replace(@carikodlar,' ',''))>0
set @EkParam=' where  C.MUSKOD in ('+@carikodlar+') '

if len(replace(@saticilar,' ',''))>0
begin
 if len(@EkParam)>0
 set @EkParam=@EkParam+' or A.SaticiKodu in ('+@saticilar+')'
 else 
set @EkParam=' where A.SaticiKodu in ('+@saticilar+')'

end

set @PVT=N'select * from (
select   C.MUSKOD,A.MUSADI,A.MUSIL,A.MUSILCE,A.SaticiAdi,C.BAACIKLA ,SUM(C.BORC-C.ALACAK) AS TUTAR from ACARI AS C
JOIN AMUSTERI AS A ON A.MUSKOD=C.MUSKOD '+@EkParam+'
GROUP BY  C.MUSKOD,A.MUSADI,A.MUSIL,A.MUSILCE,A.SaticiAdi,C.BAACIKLA) as PivotT
pivot ( Sum(TUTAR) for BAACIKLA in ('+@PivotKolonlar+') ) as pvt
'
EXECUTE (@PVT) 
 
end



