SQL change a table into another format

I have a table like this:

Faculty  Program                Number_of_enrolled
Science  Financial Modelling    25
Science  Actuarial Science      30
Science  Statistics             28
Science  Biology                16

I want to use SQL to change the table into something like this:

Faculty  Financial Modelling  Actuarial Science  Statistics  Biology
Science  25                   30                 28          16

What should I do?

Answer

Use conditional aggregation:

select 
    faculty,
    max(case when program = 'Financial Modelling' then number_of_enrolled end) [Financial Modelling],
    max(case when program = 'Actuarial Science' then number_of_enrolled end) [Actuarial Science],
    max(case when program = 'Statistics' then number_of_enrolled end) Statistics,
    max(case when program = 'Biology' then number_of_enrolled end) Biology
from mytable 
group by faculty