Convert rows into columns in MySQL Query

Question

I am getting above result from this query

   SELECT sa.Student_Id,
   CONCAT_WS(' ', es.Student_FName, es.Student_LName) AS Full_Name, 
   sa.Attendance_Date ,  
   sa.Attendance_Status 
   FROM student_attendance sa 
   INNER JOIN courses c 
     ON c.Course_Id = sa.Course_Id
   INNER JOIN campus ca 
     ON ca.Campus_Id = c.Campus_Id
   INNER JOIN students es 
     ON es.Student_Id =  sa.Student_Id
   WHERE sa.Course_Id = 1  
     ORDER BY sa.Attendance_Date DESC;

but i want this result

Can someone Do this for me Please..

Possible duplicate of [Mysql query to dynamically convert rows to columns on the basis of two columns](http://stackoverflow.com/questions/17964078/mysql-query-to-dynamically-convert-rows-to-columns-on-the-basis-of-two-columns) — STaefi, Mar 01 '16 at 07:38

Ankit Agrawal · Answer 1 · 2016-03-01T07:37:04.410

     select max(student|_id),full_name as student_FName ,
     case attendance_date when attendance_date ='03-02-2016' and attendance_status =0 
     and full_name ='Shehriar Khan'
     Then 'p' end as '3/1/2016',
     case attendance_status when attendance_date ='03-01-2016' 
     and attendance_status =0 and full_name ='Ahtesham Illahi'
     Then 'p' end as '3/2/2016'
     from table_name 
     group_by full_name,attendance_date,attendance_stauts

Mobasher Fasihy · Answer 2 · 2016-03-01T10:18:00.873

Try like this

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
     'MAX(IF(a.attendance_status = ''',
        attendance_status,
        ''', IF(a.attendance_status = "1","P","X"), NULL)) AS "',
        attendance_date,'"'
     )
   ) INTO @sql
FROM student_attendance;


SET @sql = CONCAT('SELECT a.Student_ID,a.student_name,', @sql, ' 
               FROM student_attendance a
               LEFT JOIN student_attendance AS b
                ON a.Student_ID=b.Student_ID GROUP BY a.Student_ID,b.Student_ID');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

Fiddle link

Convert rows into columns in MySQL Query

2 Answers2