如何在MySQL中高效地将动态行转换为列

CREATE TEMPORARY TABLE temp_table (id INT PRIMARY KEY AUTO_INCREMENT, column_name VARCHAR(100), column_value VARCHAR(100));

SET @sql = CONCAT('INSERT INTO temp_table (column_name, column_value) VALUES ',
(SELECT GROUP_CONCAT(CONCAT('(\'', column_name, '\', \'', column_value, '\')') SEPARATOR ', ')
FROM dynamic_table)
);
PREPARE stmt FROM @sql;
EXECUTE stmt;

SET @sql = CONCAT('SELECT ',
(SELECT GROUP_CONCAT(DISTINCT CONCAT('MAX(CASE WHEN column_name = \'', column_name, '\' THEN column_value END) AS `', column_name, '`'))
FROM temp_table),
' FROM temp_table
GROUP BY id'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;