hi,
Jonathan Moore did you find solution to creating DYNAMIC columns using Pivot table or Stored procedure or prepared statements?
I spent some time to do same you were going to do with no luck. As for semi colon issue this can be easily HACKED in the code (/reports/customsql/edit_form.php) by removing/changing the restrictions in the source code but still it does not work.
for example:
SET @s = 'SELECT * FROM prefix_assign';
PREPARE stmt3 FROM @s;
EXECUTE stmt3;
ad hoc report allows its execution even though it gives following error
Error when executing the query: Error reading from database You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'PREPARE stmt3 FROM @s; EXECUTE stmt3; LIMIT 0, 2' at line 2 SET @s = 'SELECT * FROM mdl_assign'; PREPARE stmt3 FROM @s; EXECUTE stmt3; LIMIT 0, 2 [array ]