Generate a dynamic pivot table in MySQL

Question

I have a table named mdl_grade_grades with columns id, userid, itemid, rawgrademax, finalgrade I have another table mdl_grade_items with id, itemname where itemid in mdl_grade_grades = id in mdl_grade_items

I am using MySQL database and number of items will differ in different cases.

I have researched a lot for last 3 days but my MySQL skills are not that enriched to understand and modify the related solutions available. Still I tried to modify the queries provided in following threads: MySQL dynamic pivot table MySQL Table pivot - Dynamic

But, I get an error near "PREPARE stmt FROM @sql;"

I tried this:

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'max(case when itemid = ''',
      itemid,
      ''' then finalgrade end) AS `',
      itemid, '`'
    )
  ) INTO @sql
FROM  mdl_grade_grades;

SET @sql = CONCAT('SELECT userid, ', @sql, ' 
                  FROM mdl_grade_grades 
                  GROUP BY userid');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

I want output like shown below Desired Output

So, the item names will become headers and their value will be equal to (finalgrade / rawgrademax) * 100 whereever user has a record. If user does not have a record for the item, the value for that item will remain blank.

Seriously consider handling issues of data display in application code — Strawberry, Mar 31 '19 at 09:31
May you live in peace Strawberry! Thanks for your comment. Actually, the data retrieved is so large that handling the display will require multiple queries and cause long delays in generating the output. That's why we need to get data in a single query and then output it to a file. — Mahtab Hussain, Apr 04 '19 at 05:06

score 0 · Answer 1 · answered Mar 31 '19 at 08:38

your error can remove from this code rest you can understand wisely

SET @sql = NULL;
    SELECT GROUP_CONCAT(DISTINCT CONCAT( ' MAX(CASE ',
                                itemid,
                                ' WHEN ''',
                                '' ,
                                ''' THEN ',
                                finalgrade,
                                '  END) ''',
                                itemid,
                                '''') )
        INTO @sql
        FROM mdl_grade_grades;



SET @sql = CONCAT('SELECT userid, ', @sql, ' 
                  FROM mdl_grade_grades 
                  GROUP BY userid');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

May you live in peace Vishal! Thanks for looking into the issue. I tried above query and get this error. "PREPARE stmt FROM @sql #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'W FROM mdl_grade_grades GROUP BY userid' at line 1" — Mahtab Hussain, Apr 01 '19 at 11:41

Generate a dynamic pivot table in MySQL

1 Answers1