Postgres 13

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE mytable
    ("student" text, "course" text, "grade" int);
    
INSERT INTO mytable VALUES
    ('a-student', 'ENG-W05', 100),
    ('a-student', 'MAT-W05', 85),
    ('a-student', 'ENG-W06', 100),
    ('b-student', 'MAT-W05', 90),
    ('b-student', 'SCI-W05', 75);
    
SELECT * FROM mytable
5 rows affected



student
course
grade


a-student
ENG-W05
100

a-student
MAT-W05
85

a-student
ENG-W06
100

b-student
MAT-W05
90

b-student
SCI-W05
75


SELECT
    student,
    SUM(grade) FILTER (WHERE course = 'ENG-W05') as eng_w05, 
    SUM(grade) FILTER (WHERE course = 'MAT-W05') as mat_w05, 
    SUM(grade) FILTER (WHERE course = 'ENG-W06') as eng_w06, 
    SUM(grade) FILTER (WHERE course = 'SCI-W05') as sci_w05
FROM mytable
GROUP BY student


student
eng_w05
mat_w05
eng_w06
sci_w05


a-student
100
85
100
null

b-student
null
90
null
75


SELECT
    student,
    SUM(
       CASE 
           WHEN course = 'ENG-W05' THEN grade
       END
    ) AS eng_w05, 
    SUM(
       CASE 
           WHEN course = 'MAT-W05' THEN grade
       END
    ) AS mat_w05,
    SUM(
       CASE 
           WHEN course = 'ENG-W06' THEN grade
       END
    ) AS eng_w06,
    SUM(
       CASE 
           WHEN course = 'SCI-W05' THEN grade
       END
    ) AS sci_w05
FROM mytable
GROUP BY student


student
eng_w05
mat_w05
eng_w06
sci_w05


a-student
100
85
100
null

b-student
null
90
null
75

student	course	grade
a-student	ENG-W05	100
a-student	MAT-W05	85
a-student	ENG-W06	100
b-student	MAT-W05	90
b-student	SCI-W05	75