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?

If someone later adds a new row with the date 2020-04-23, do you want a new column suddenly? — jarlh, Apr 23 '20 at 08:37
Using php i have used filter for date. user can enter a date by his choice. — runjun das, Apr 23 '20 at 08:41
Consider handling issues of data display in application code — Strawberry, Apr 23 '20 at 08:49

zealous · Answer 1 · 2020-04-23T09:04:05.663

0

try the following, here is the DEMO. I have not included s_id as it does not make any sense here to include it.

select
    student_name,
    max(case when attendance_date='2020-01-01' then status end) as '01-01-2020',
    max(case when attendance_date='2020-01-02' then status end) as '01-02-2020'
from yourTable
group by
    student_name

edited Apr 23 '20 at 09:04

answered Apr 23 '20 at 08:43

zealous

7,336
4
16
36

Without a `group by` clause, this is not a valid query. – GMB Apr 23 '20 at 08:44
thank you so much for your responds. it showing sql error:Invalid use of group function – runjun das Apr 23 '20 at 09:03

score 0 · Accepted Answer · answered Apr 23 '20 at 08:43

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

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

2 Answers2