SQL Pivot on multiple columns with INT and NVARCHAR datatypes

Question

New to SQL Pivots with multiple columns. I want to Pivot Table 1 to look like Table 2, pivoting on multiple columns (teach_last, overall_mark, overall_pct) for the subject. Any advice is appreciated.

Table 1

Table 2

Answer 1

You can use conditional aggregation:

select student_id,
       max(case when subject = 'Math' then teach_last end) as math_teach_last,
       max(case when subject = 'Math' then overall_mark end) as math_overall_mark,
       max(case when subject = 'Math' then overall_pc end) as math_overall_pct,
       max(case when subject = 'Science' then teach_last end) as science_teach_last,
       max(case when subject = 'Science' then overall_mark end) as science_overall_mark,
       max(case when subject = 'Science' then overall_pc end) as science_overall_pct,
       . . .
from t
group by student_id;

Answer 2

You can also use nested selects to pivot the table without aggregations:

select distinct student_id,
    (select teach_last from students s1 where subject = 'Math' and s.student_id = s1.student_id) as [Math_teacher_last],
    (select overall_mark from students s1 where subject = 'Math' and s.student_id = s1.student_id) as [Math_overall_mark],
    (select overall_pct from students s1 where subject = 'Math' and s.student_id = s1.student_id) as [Math_overall_pct],
    (select teach_last from students s1 where subject = 'Science' and s.student_id = s1.student_id) as [Science_teacher_last],
    (select overall_mark from students s1 where subject = 'Science' and s.student_id = s1.student_id) as [Science_overall_mark],
    (select overall_pct from students s1 where subject = 'Science' and s.student_id = s1.student_id) as [Science_overall_pct],
    ...    
    from students s