How to Create Dynamic Pivot Tables with Integer Column Names in MySQL?

Dynamic Pivot Tables in MySQL

When attempting to generate a pivot table with dynamic columns, it is crucial to ensure that the column names are handled correctly. In the provided situation, the user_id is an integer, which can cause issues if not formatted properly.

To address this, it is necessary to wrap the user_id values in backticks (`) to specify them as column names and prevent their misinterpretation as numeric values. Here's the modified 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;

This modification ensures that the user_id values are treated as column names, resulting in a pivot table with dynamic columns representing each unique user_id and their corresponding scores.

The above is the detailed content of How to Create Dynamic Pivot Tables with Integer Column Names in MySQL?. For more information, please follow other related articles on the PHP Chinese website!