How to get row values as columns using Sql Query CASE statement

Question

I have a table

s_id   student_name attendance_date status
1        student1     2020-01-01     P
2        student1     2020-01-02     P
3        student2     2020-01-01     P
4        student2     2020-01-02     A

I want my table as

s_id    student_name   01-01-2020   02-01-2020
1       student1       P             P
2       student2       P             A

I have tried using CASE WHEN statement but students names are keep repeating. How can i get the result without repeating the names of student?

Answer 1

To pivot over a fixed list of dates, you can do conditional aggregation:

select
    s_id, 
    student_name,
    max(case when attendance_date = '2020-01-01' then status end) `2020-01-01`,
    max(case when attendance_date = '2020-01-02' then status end) `2020-01-02`
from mytable
group by s_id, student_name
order by s_id