Pivot on Dependent Multiple Columns

Question

How would I pivot based on multiple dependent columns instead of a single dependent column?

Super_Location Location    Year   Min    Value
USA            Primary     2020   0.00   0
Brazil         Secondary   2019   5      0.5
USA            Primary     2019   2      0.33

Output Table Example

Super_Location Primary_Min_2020  Primary_Min_2019 Secondary_Min_2020 Secondary_Min_2019 Primary_Division_2020 Primary_Division_2019 Secondary_Division_2020 Secondary_Division_2019

USA            0.00              2                NULL               NULL               0                     0.33                  NULL                    NULL
Brazil         NULL              NULL             NULL               5                  NULL                  NULL                  NULL                    0.5       

Based on help with another example, I know pivoting is the right direction and attempted to run the following query. Unfortunately, I am getting too many error messages. Am I close at all with the below query?

Select *
 From  ( 
            Select Super_Location
                  ,Item = concat(coalesce(Location, Year, Min, 'NULL'),'_Min_Value_Year')
                  ,Value = Min, Value, Year
             From TestTable
       ) src
 Pivot (sum(Min, Value) for Item in ( [Primary_Min_2020 ],[Primary_Min_2019],[Secondary_Min_2020], [Secondary_Min_2019],[Primary_Division_2020],[Secondary_Division_2020], [Secondary_Division_2019], [NULL_Min], [NULL_Value] ) ) pvt

For reference, I am trying to use what I have learned from my previous question, located here. Converting Multiple Rows to Columns

Answer 1

You can use a CROSS APPLY to unpivot/expand the rows, and then pivot the results.

Example

Select *
 From  (
        Select A.Super_Location
              ,B.*
         From  TestTable A
         Cross Apply ( values ( concat(location,'_Min_',year),[Min] ) 
                             ,( concat(location,'_Division_',year),[Value] ) 
                     ) B(Item,Value)
       ) src
 Pivot (max(Value) for Item in ( [Primary_Min_2020 ],[Primary_Min_2019],[Secondary_Min_2020], [Secondary_Min_2019],[Primary_Division_2020],[Secondary_Division_2020], [Secondary_Division_2019]  ) ) pvt

Results

---

NOTE: If it helps with the visualization, the subquery generates the following: