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

Transact-SQL
, ,
1

I work on sql server 2014 i need to pivot data for multi column as supplier percentage and material percentage

my sample data as below :

CREATE TABLE [dbo].[TempMaterial](
[CompanyID] [bigint] NOT NULL,
[Year] [int] NOT NULL,
[Rev_ID] [int] NULL,
[MetarialID] [int] NULL,
[Metarialperc] [float] NULL,
[SupplierPerc] [decimal](18, 2) NULL
) ON [PRIMARY]

GO
---select * from [dbo].[TempMaterial]
insert into [dbo].[TempMaterial](CompanyID,Year,Rev_ID,MetarialID,Metarialperc,SupplierPerc)
select 1039152, 2020,339898,1888574,80.18,79.18
union
select 1039152, 2020,339898,1888575,100,99.00
union
select 1039152, 2020,339898,1888576,98.87,96.87
union
select 1039152, 2020,339898,1888577,93.74,90.74
union


select 1039109, 2021,339820,1888574,83.18,80.18
union
select 1039109, 2021,339820,1888575,97.05,99.00
union
select 1039109, 2021,339820,1888576,92.87,94.87
union
select 1039109, 2021,339820,1888577,95.74,96.74

select * from [dbo].[TempMaterial]

Expected result as below

CompanyID Year Rev_ID MetarialID1888574 MetarialID1888575 MetarialID1888576 MetarialID1888577 supplier1888574 supplier1888575 supplier1888576 supplier1888577
1039109 2021 339820 83.18 97.05 92.87 95.74 80.18 99.00 94.87 96.74
1039152 2020 339898 80.18 100 98.87 93.74 79.18 99.00 96.87 90.74

so can you help me make it dynamically please for both column

2

to clear what i need

i need

company and year and rev then pivot first Colum based on material id ,pivot second Colum based on material id

with another meaning

material id is four for first column material percentage,material id is four for second column supplier percentage

material percentage for every material related,supplier percentage for every material related

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
')