First step is to UNPIVOT your starting data (note I am using a temporary table for your table in these examples). Here is the reference documentation
Using PIVOT and UNPIVOT - SQL Server | Microsoft Docs[
^]
SELECT CompanyID,Year,Rev_ID,MetarialID, ValueType, x
into #temp1
FROM
(SELECT CompanyID,Year,Rev_ID,MetarialID, Metarialperc,SupplierPerc
FROM #TempMaterial) p
UNPIVOT
(x FOR ValueType IN
(Metarialperc,SupplierPerc)
)AS unpvt;
This puts these values into table #temp1
CompanyID Year Rev_ID MetarialID ValueType x
1039109 2021 339820 1888574 Metarialperc 83.18
1039109 2021 339820 1888574 SupplierPerc 80.18
1039109 2021 339820 1888575 Metarialperc 97.05
1039109 2021 339820 1888575 SupplierPerc 99.00
1039109 2021 339820 1888576 Metarialperc 92.87
1039109 2021 339820 1888576 SupplierPerc 94.87
1039109 2021 339820 1888577 Metarialperc 95.74
1039109 2021 339820 1888577 SupplierPerc 96.74
1039152 2020 339898 1888574 Metarialperc 80.18
1039152 2020 339898 1888574 SupplierPerc 79.18
1039152 2020 339898 1888575 Metarialperc 100.00
1039152 2020 339898 1888575 SupplierPerc 99.00
1039152 2020 339898 1888576 Metarialperc 98.87
1039152 2020 339898 1888576 SupplierPerc 96.87
1039152 2020 339898 1888577 Metarialperc 93.74
1039152 2020 339898 1888577 SupplierPerc 90.74
Step 2 is to provide an interim step to work out our final column names
select CompanyID,Year,Rev_ID,valueType, MetarialID, replace(valueType,'perc','') + cast(MetarialID as varchar(255)) as finalCol, x
into #temp2
FROM #temp1
Table
#temp2
now contains the same data as
#temp1
but with an additional column
finalcol
containing values Metarial1888574, Metarial1888575, Metarial1888576, Metarial1888577, supplier1888574, supplier1888575, supplier1888576, supplier1888577
I can now PIVOT table
#temp2
to get the expected values you have listed
select * from
(
select CompanyID,Year,Rev_ID,finalCol, x
FROM #temp2
) src1
PIVOT
(
max(x) for finalCol in (Metarial1888574, Metarial1888575, Metarial1888576, Metarial1888577,
supplier1888574,supplier1888575, supplier1888576, supplier1888577)
) pvt1
Assuming your use of the word "dynamically" in your question means literally that, then you are going to have to generate this as dynamic SQL - There are several examples on how to do that on
dynamic query in sql - Google Search[
^]
There is an example of generating the list of column names (as well as some simple Dynamic SQL) in my article
Processing Loops in SQL Server[
^]