0

This question already has an answer here:

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?

marked as duplicate by Julien Vavasseur, Paul White Mar 2 '16 at 6:37

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

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 
 ;

Not the answer you're looking for? Browse other questions tagged or ask your own question.