mysql列转行函数

CREATE TABLE students (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    math_score INT,
    english_score INT,
    science_score INT
);

SELECT 
    id,
    name,
    MAX(CASE WHEN subject = 'math' THEN score END) AS math_score,
    MAX(CASE WHEN subject = 'english' THEN score END) AS english_score,
    MAX(CASE WHEN subject = 'science' THEN score END) AS science_score
FROM (
    SELECT 
        id,
        name,
        'math' AS subject,
        math_score AS score
    FROM students
    UNION ALL
    SELECT 
        id,
        name,
        'english' AS subject,
        english_score AS score
    FROM students
    UNION ALL
    SELECT 
        id,
        name,
        'science' AS subject,
        science_score AS score
    FROM students
) AS temp
GROUP BY id, name;