0

I need help with my mysql pivot code. Everytime I add WHERE MONTH(attendance_date) = 2 inside @sql_dynamic gets error. pls help

I have a code:

SET @sql_dynamic:= (SELECT GROUP_CONCAT(DISTINCT CONCAT('if(sum(if(attendance_date = "',
                    date_format(attendance_date, '%Y-%m-%d'),
                    '",1,0))=0,0,attendance_status) AS `',
                    date_format(attendance_date, '%M-%d'),'`'
                    )
                ) from attendance
              );

SET @sql = CONCAT('SELECT studentidnumber, student_fullname, subject_id, ', 
    @sql_dynamic, ' 
FROM attendance
WHERE subject_id=1

GROUP BY studentidnumber'
);

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt
  • Please add the error message. And I cannot see WHERE MONTH(attendance_date) = 2 in your dynamic query, please add the code that generates the error. – McNets Jan 20 '19 at 10:51
  • i finally got the correct answer. its just that the phpmyadmin cannot parse the query. i made it through mysql command line. and its working – Sanoj Senar Jan 20 '19 at 15:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.