How can I add a dynamic subquery in MySQL

Question

I have the following query that retrieves student marks, I want the query to get the list of assessments (in the CASE lines) dynamically, because sometimes the number of assessments might differ, in some instances there might be 2 tests or 3 tests. In the current query the number of assessments are static (3), I want the query to be flexible enough to dynamically get the number of assessments.

SELECT
    student_subject_averages.student_average,
    student_subject_averages.ca_average,
    student_subject_averages.exam_mark,
    subjects.subject_name, 
    subjects.subject_type, 
    subjects.id as subject_id,
    users.name,
    users.salutation,
    users.lastname,
    (AVG( CASE WHEN assessements.id = 1 AND assessements.term_id = 2 THEN (marks.mark) END)) as Test1,
     (AVG( CASE WHEN assessements.id = 2 AND assessements.term_id = 2 THEN (marks.mark) END)) as Test2,
     (AVG( CASE WHEN assessements.id = 2 AND assessements.term_id = 2 THEN (marks.mark) END)) as Test2
    
    FROM
    marks
    INNER JOIN assessements ON assessements.id = marks.assessement_id
    INNER JOIN teaching_loads ON teaching_loads.id = marks.teaching_load_id
    INNER JOIN subjects ON subjects.id = teaching_loads.subject_id
    INNER JOIN users ON users.id = marks.teacher_id
     INNER JOIN student_subject_averages ON student_subject_averages.student_id = marks.student_id
    WHERE marks.student_id = 1232 AND `assessements`.`term_id` = 2 AND marks.active=1 AND student_subject_averages.teaching_load_id=marks.teaching_load_id
    GROUP BY
    marks.student_id,student_subject_averages.student_id,
    subjects.

How can I tweak my code to allow for dynamic generation of the assessements here

  (AVG( CASE WHEN assessements.id = 2 AND assessements.term_id = 2 THEN (marks.mark) END)) as Test2