MySQL queries and procedures for gibbon

gibbon_markbook_query.sql

-- Procedure to export grades for a year and class from the markbook

DROP PROCEDURE IF EXISTS customMarkbookGrades;

DELIMITER $$
CREATE PROCEDURE customMarkbookGrades (
                 IN school_year_id int(3),
                 IN year_group VARCHAR(256),
                 IN class_name VARCHAR(256),
                 IN grade_type VARCHAR(256)
                  )
BEGIN
    SET @PivotQuery = NULL;
    SELECT
        GROUP_CONCAT( 
            CONCAT(
                ' MAX(IF(course.nameShort = ''',
                t.CourseName,
                ''', entry.attainmentValue, NULL)) AS ',
                t.CourseName
            )
        )  into @PivotQuery
    FROM
        (SELECT 
            course.nameShort as CourseName
        FROM
            gibbonYearGroup as year_group,
            gibbonCourse as course,
            gibbonCourseClass as course_class
        WHERE
            year_group.gibbonYearGroupID = course.gibbonYearGroupIDList AND
            course.gibbonCourseID = course_class.gibbonCourseID AND
            course.gibbonSchoolYearID = school_year_id AND
            year_group.nameShort = year_group AND
            course_class.nameShort LIKE CONCAT('%', class_name, '%')
        ORDER BY course.nameShort ASC) t;
    
    SET @PivotQuery = CONCAT('
    SELECT 
        person.surname as Nachname,
        person.firstName as Vorname,
        person.dob as Geburtsdatum,
        person.nationalIDCardNumber as Cedula,
        MAX(year_group.name) as Jahrgang,
        MAX(course_class.name) as Klasse,
        MAX(mark.type) as \'Noten Typ\',
        ', @PivotQuery, '
    FROM
        gibbonYearGroup as year_group
    JOIN gibbonCourse as course
    ON year_group.gibbonYearGroupID = course.gibbonYearGroupIDList
    JOIN gibbonCourseClass as course_class
    ON course.gibbonCourseID = course_class.gibbonCourseID
    LEFT JOIN gibbonMarkbookColumn as mark
    ON mark.gibbonCourseClassID = course_class.gibbonCourseClassID
    LEFT JOIN gibbonMarkbookEntry as entry
    ON mark.gibbonMarkbookColumnID = entry.gibbonMarkbookColumnID
    LEFT JOIN gibbonPerson as person
    ON entry.gibbonPersonIDStudent = person.gibbonPersonID
    WHERE
        entry.gibbonPersonIDStudent != "" AND
        course.gibbonSchoolYearID = ', school_year_id, ' AND
        year_group.nameShort = \'', year_group, '\' AND
        course_class.nameShort LIKE \'%', class_name, '%\' AND
        mark.type = \'', grade_type, '\'
    GROUP BY entry.gibbonPersonIDStudent
    ORDER BY person.surname
    ');
    
    PREPARE statement FROM @PivotQuery;
    EXECUTE statement;
    DEALLOCATE PREPARE statement;
END$$
DELIMITER ;

Must stuff is from https://stackoverflow.com/questions/12598120/mysql-pivot-table-query-with-dynamic-columns