dunianyaasupna

ALTER PROCEDURE [dbo].[5upr1_sp_cobaCoba] 
(	
	-- Add the parameters for the function here
@calMonth varchar(2)
,@calYear varchar(4)
) AS
DECLARE @cmd varchar(max)
,@columns varchar(max)

set @columns = ''
select @columns = @columns + '['+cmp_CompanyName+'],'  from masters.Company where cmp_ParentCompany = '6001' and  cmp_ParentCompany <> cmp_CompanyCode
select @columns = substring(@columns, 0, len(@columns))

set @cmd = 'select * from (
SELECT bss_ID      
      ,bss_CalYear
      ,bss_CalMonth
      ,bss_DescriptionCode
      ,dec_DescriptionName
      ,cmp_CompanyName
      ,bss_YTDBudget
  FROM [reports].[BalanceSheetSummary] a
  left join masters.Company b on a.bss_EntityID = b.cmp_CompanyCode 
  left join masters.DescriptionCode c on a.bss_DescriptionCode = c.dec_DescriptionCode
  where bss_CalMonth = '+ @calMonth +' and bss_CalYear =' +  @calYear +'
  ) p
  PIVOT
  (sum(bss_YTDBudget) FOR cmp_CompanyName IN (  
  '+ @columns +'
  )) bud order by bss_DescriptionCode'  
 exec (@cmd)