How to Create Dynamic Pivot Tables in MySQL with Integer User IDs?

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.

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