How can a pivot table be created in MySQL to display attendance data according to weekdays of the week?

edit

Assuming the attendance data is stored in a table named "attendance" with columns "id", "date", "studentname" and "presentstatus", the following SQL query can be used to create a pivot table that displays attendance data for each weekday:

SELECT
  COUNT(CASE WHEN DAYOFWEEK(date) = 1 THEN present_status END) AS 'Sunday',
  COUNT(CASE WHEN DAYOFWEEK(date) = 2 THEN present_status END) AS 'Monday',
  COUNT(CASE WHEN DAYOFWEEK(date) = 3 THEN present_status END) AS 'Tuesday',
  COUNT(CASE WHEN DAYOFWEEK(date) = 4 THEN present_status END) AS 'Wednesday',
  COUNT(CASE WHEN DAYOFWEEK(date) = 5 THEN present_status END) AS 'Thursday',
  COUNT(CASE WHEN DAYOFWEEK(date) = 6 THEN present_status END) AS 'Friday',
  COUNT(CASE WHEN DAYOFWEEK(date) = 7 THEN present_status END) AS 'Saturday'
FROM attendance
GROUP BY student_name

The query uses the DAYOFWEEK function to extract the weekday number from the date column, and then uses the COUNT function with a conditional statement (CASE) to count the number of times a student was present on each weekday. The result is grouped by student_name to show the pivot table.