0

I have two tables Student_Attendance and Students.

I am using this query:

SELECT
    sa.Student_Id, 
    sa.Attendance_Date, 
    CASE WHEN sa.Attendance_Status as Status
FROM student_attendance sa 
INNER JOIN courses c 
    ON c.Course_Id = sa.Course_Id
INNER JOIN students es 
    ON es.Student_Id =  sa.Student_Id
WHERE sa.Course_Id = 1 
ORDER BY sa.Attendance_Date DESC;

I am getting this result from above query:

enter image description here

But I want something like below:

enter image description here

In the student_attendance table there are more than 50 rows.

How 7 attendance_date rows should be converted into 7 columns as I have given below?

Can someone help me please?

Improve this question
0

1 Answer 1

Reset to default
1

You must add a GROUP BY and an aggregate around the CASE statement(s). There is no PIVOT operator with MySQL.

Query:

SELECT sa.Student_Id 
    , sa.Attendance_Date
    , MAX(CASE WHEN sa.Attendance_Date = '20160203' Then sa.Attendance_Status END) As '20160203'
    , MAX(CASE WHEN sa.Attendance_Date = '20160204' Then sa.Attendance_Status END) As '20160204'
    , MAX(CASE WHEN sa.Attendance_Date = '20160205' Then sa.Attendance_Status END) As '20160205'
    , MAX(CASE WHEN sa.Attendance_Date = '20160206' Then sa.Attendance_Status END) As '20160206'
 FROM student_attendance sa 
 INNER JOIN courses c 
     ON c.Course_Id = sa.Course_Id
 INNER JOIN students es 
     ON es.Student_Id =  sa.Student_Id
 WHERE sa.Course_Id = 1 
 ;
Improve this answer

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.