mysql 整张表行转列

CREATE TABLE student (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    subject VARCHAR(50),
    score INT
);

INSERT INTO student (id, name, subject, score) VALUES
(1, 'Alice', 'Math', 90),
(1, 'Alice', 'English', 85),
(2, 'Bob', 'Math', 88),
(2, 'Bob', 'English', 92);

SELECT name,
       MAX(CASE WHEN subject = 'Math' THEN score END) AS Math,
       MAX(CASE WHEN subject = 'English' THEN score END) AS English
FROM student
GROUP BY name;

SELECT name,
       COALESCE(MAX(CASE WHEN subject = 'Math' THEN score END), 0) AS Math,
       COALESCE(MAX(CASE WHEN subject = 'English' THEN score END), 0) AS English
FROM student
GROUP BY name;

SET @sql = NULL;
SELECT GROUP_CONCAT(DISTINCT CONCAT('MAX(CASE WHEN subject = ''', subject, ''' THEN score END) AS ', subject)) INTO @sql
FROM student;

SET @sql = CONCAT('SELECT name, ', @sql, ' FROM student GROUP BY name');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;