Ali Shaikh Ali Shaikh - 6 months ago 33
PHP Question

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

Answer

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