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

MySQL Dynamic Pivot Table with Integer Column Names

Creating a dynamic pivot table with integer column names in MySQL poses a slight challenge. When running the provided query, you may encounter an error if the user_id values are integers. To resolve this issue, we need to wrap the user_id values in backticks (`).

The adjusted SQL query looks like this:

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;

The key modification here is the addition of backticks around the user_id values in the GROUP_CONCAT statement. By doing this, we are ensuring that the user_id values are treated as strings instead of column names.

SQL Fiddle Demo: https://sqlfiddle.com/#!2/eab24/4

Now, even with integer user_id values, the dynamic pivot table will be generated successfully.

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