这里有一个选项 conditional aggregation :

select subject, 
       max(case when exam = 'Mid1' then marks end) as Mid1,
       max(case when exam = 'Mid2' then marks end) as Mid2
from yourtable
group by subject