如何在 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.

以上是如何在 MySQL 中建立具有整數列名的動態資料透視表？的詳細內容。更多資訊請關注PHP中文網其他相關文章！