Pivot Tables in MySQL with Dynamic Columns

This question addresses the challenge of creating MySQL pivot tables with dynamic columns. While the solution works effectively when user_id is defined as a string, it fails when encountering integer values.

The provided code snippet illustrates the initial attempt at creating the pivot table. However, to resolve the issue related to integer user_id values, the corrections lie in the way the column names are constructed within the dynamic SQL query.

The original code:

...
GROUP_CONCAT(DISTINCT
    CONCAT(
      'max(case when user_id = ''',
      user_id,
      ''' then score end) AS ',
      user_id
    )
  ) INTO @sql

int user_id values are in causing the problem since they are being used directly as column names. To address this, these values need to be wrapped in backticks (`). The corrected code adjusts this:

...
GROUP_CONCAT(DISTINCT
    CONCAT(
      'max(case when user_id = ''',
      user_id,
      ''' then score end) AS `',
      user_id, '`'
    )
  ) INTO @sql

The final, corrected query:

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'max(case when user_id = ''',
      user_id,
      ''' then score end) AS `',
      user_id, '`'
    )
  ) INTO @sql
FROM  measure2;

SET @sql = CONCAT('SELECT inspection_date, ', @sql, ' 
                  FROM measure2 
                  GROUP BY inspection_date');

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

With these adjustments, the pivot table can now be generated successfully even when dealing with integer user_id values.