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

Dynamic Pivot Table in MySQL

When working with a database, it is often useful to create pivot tables to summarize data across multiple columns. However, when the column names are dynamic, it can present a challenge. This article explores how to create a dynamic pivot table in MySQL, particularly when the user IDs are integers.

In the example provided by the user, the query failed when the user ID was an integer, but worked fine when it was a string. The issue arose because the values were being used as column names directly. To resolve this, it is necessary to wrap the values in backticks (`) to create valid column names.

For instance, instead of:

max(case when user_id = 1 then score end) as 1

The corrected syntax would be:

max(case when user_id = 1 then score end) as `1`

Additionally, when selecting the columns dynamically, the backticks must also be included. The corrected query would look like:

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;

By making these adjustments, the pivot table can be generated successfully, even when the user IDs are integers.

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