Hello afaherty ,

I got your point, You want to have a generic query like dynamic pivoting solution. If this is the case Power Query would be the best approach . I am pasting couple of option you can opt for :

1. Power Query : the true generic one 

let
Source = #"Your Data Source Table Name",

// Unpivot the Score and Category columns to create a single 'Value' column
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(
Source,
{"StudentID", "Level", "Test"},
"Metric Type",
"Value"
),

// Create dynamic column name: [Test] for score, [Test] Category for category
#"Added Final Pivot Column Name" = Table.AddColumn(#"Unpivoted Other Columns", "Pivot Column Name", each
if [Metric Type] = "Score" then
[Test]
else
[Test] & " Category"
),

// Dynamic Pivot step: This is the generic part!
#"Pivoted Column" = Table.Pivot(
#"Added Final Pivot Column Name",
List.Distinct(#"Added Final Pivot Column Name"[Pivot Column Name]),
"Pivot Column Name",
"Value",
List.Max
)
in
#"Pivoted Column"

2. DAX : You can use external tool to generate repeating string

Pivoted_Report =
SUMMARIZECOLUMNS(
'Test'[StudentID],
'Test'[Level],
"Math",
CALCULATE( MAXX('Test', 'Test'[Score]), 'Test'[Test] = "Math" ),
"Math Category",
CALCULATE( MAXX('Test', 'Test'[Category]), 'Test'[Test] = "Math" ),

"English",
CALCULATE( MAXX('Test', 'Test'[Score]), 'Test'[Test] = "English" ),
"English Category",
CALCULATE( MAXX('Test', 'Test'[Category]), 'Test'[Test] = "English" )

// ... repeat for all other subjects
)

I hope this helps .

Cheers.