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
-
MS SQL Server 18Stefanie Mircovich– Stefanie Mircovich2021-01-26 21:31:26 +00:00Commented Jan 26, 2021 at 21:31
-
you may check this out.Barbaros Özhan– Barbaros Özhan2021-01-26 21:32:48 +00:00Commented Jan 26, 2021 at 21:32
Add a comment
|
2 Answers
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;
Comments
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
