Search code examples
phpmysqlpivot-tabletime-and-attendance

MySQL pivot tables - rows to colums

This Query 

SELECT
    Student.StudentID,
    student.`Name`,
    attendance.Date,
    CASE
WHEN attendance.StudentID IS NOT NULL THEN
    'Present'
ELSE
    'Absent'
END AS Attendance_Status
FROM
    student
LEFT JOIN attendance ON student.StudentID = attendance.StudentID

give me this result

Figure 1

How can I get a result similar to this

Figure 2

Solution

  • Solved by this answer

    SELECT student.StudentID, student.Name,

    IF ( ( SELECT DISTINCT 1 FROM attendance WHERE attendance.StudentID = student.StudentID AND date = '2015-09-07' ) = 1, 'Present', 'Absent' ) AS 2015-09-07,

    IF ( ( SELECT DISTINCT 1 FROM attendance WHERE attendance.StudentID = student.StudentID AND date = '2015-09-14' ) = 1, 'Present', 'Absent' ) AS 2015-09-14,

    student.WorkshopID FROM student

    by @Hitesh Mundra