How to make data pivot dynamically to materialper and suplierperc both?

Transact-SQL
, ,
3

Need to use 2 pivots

DECLARE @ColsList varchar(max),
		@ColsCols varchar(max),
		@MetList varchar(max),
		@SupList varchar(max)

DECLARE @Cols TABLE (Head VARCHAR(MAX))  
  
INSERT @Cols (Head)  
SELECT DISTINCT MetarialID  
FROM #TempMaterial 
  

SELECT  @ColsCols = COALESCE(@ColsCols + ',[', '[') + Head + 'Met]'  + COALESCE(',[', '[') + Head + 'Sup]'  ,
		@ColsList = COALESCE(@ColsList + ',cast(max([', ',Cast(max([') + Head + 'Met]) as numeric(12,2)) as [' + Head + 'Met]'  + ',Cast(max([' + Head + 'Sup]) as numeric(12,2)) as [' + Head + 'Sup]' ,
		@MetList = COALESCE(@MetList + ',[', '[') + Head + 'Met]'  ,
		@SupList = COALESCE(@SupList + ',[', '[') + Head + 'Sup]'  
FROM @Cols t  
order by head desc



EXEC ('SELECT CompanyID, Year, Rev_ID ' + @ColsList + '   
FROM   
(  
        SELECT a.CompanyID, a.Year, Rev_ID, cast(MetarialID as varchar(20)) + ''Met'' as MetMetarialID, cast(MetarialID as varchar(20))  + ''Sup'' as SupMetarialID, Metarialperc, SupplierPerc
        FROM #TempMaterial a
) t  
PIVOT (sum(Metarialperc) FOR MetMetarialID IN (' + @MetList + ')) PVT 
PIVOT (sum(SupplierPerc) FOR SupMetarialID IN (' + @SupList + ')) PVT1
group by CompanyID, Year, Rev_ID
')