Analytics and reporting

After a long day I found out a solution.
It's a pity that mySQL does not have a PIVOT function but it works fine with the filter


SELECT #DISTINCT
CONCAT(u.firstname , ' ' , u.lastname) AS 'Giudice' ,
CASE
WHEN cm.module = 1 THEN (SELECT a1.name FROM prefix_assign a1 WHERE a1.id = cm.instance)
WHEN cm.module = 9 THEN (SELECT a9.name FROM prefix_forum a9 WHERE a9.id = cm.instance)
WHEN cm.module = 10 THEN (SELECT a10.name FROM prefix_glossary a10 WHERE a10.id = cm.instance)
WHEN cm.module = 13 THEN (SELECT a13.name FROM prefix_lesson a13 WHERE a13.id = cm.instance)
WHEN cm.module = 16 THEN (SELECT a16.name FROM prefix_quiz a16 WHERE a16.id = cm.instance)
WHEN cm.module = 22 THEN (SELECT a22.name FROM prefix_workshop a22 WHERE a22.id = cm.instance)
END AS 'Test',
CASE
WHEN cmc.completionstate = 0 THEN 'Running'
WHEN cmc.completionstate = 1 THEN 'Completed'
WHEN cmc.completionstate = 2 THEN 'Passed'
WHEN cmc.completionstate = 3 THEN 'Failed'
ELSE 'Unknown'
END AS 'Stato',
CASE
WHEN cm.module = 1 THEN (SELECT g1.grade FROM prefix_assign_grades g1 WHERE g1.assignment = cm.instance AND g1.userid = u.id)
WHEN cm.module = 9 THEN (SELECT g9.grade FROM prefix_forum_grades g9 WHERE g9.itemnumber = cm.instance AND g9.userid = u.id)
WHEN cm.module = 13 THEN (SELECT g13.grade FROM prefix_lesson_grades g13 WHERE g13.lessonid = cm.instance AND g13.userid = u.id)
WHEN cm.module = 16 THEN (SELECT g16.grade FROM prefix_quiz_grades g16 WHERE g16.quiz = cm.instance AND g16.userid = u.id)
WHEN cm.module = 22 THEN (SELECT g22.grade FROM prefix_workshop_grades g22 WHERE g22.assessmentid = cm.instance /*AND a22.userid = u.id*/)
END AS 'Voto'

FROM prefix_course AS c
JOIN prefix_context AS ctx ON c.id = ctx.instanceid
JOIN prefix_role_assignments AS ra ON ra.contextid = ctx.id
JOIN prefix_user AS u ON u.id = ra.userid
JOIN prefix_course_modules_completion cmc ON cmc.userid = u.id
JOIN prefix_course_modules cm ON cmc.coursemoduleid = cm.id
JOIN prefix_modules m ON cm.module = m.id

WHERE c.id = %%COURSEID%%
AND u.id IN (
SELECT userid
FROM prefix_cohort_members AS chm
WHERE chm.cohortid IN (
SELECT ch.id
FROM prefix_cohort AS ch
JOIN prefix_cohort_members AS chm ON ch.id = chm.cohortid
WHERE userid = %%USERID%%
#AND ch.id NOT IN (5, 6, 8, 12) #excluded cohort
)
)
#filters
%%FILTER_SEARCHTEXT:cm.instance:~%%