mysql查询结果横转列

CREATE TABLE sales (
    id INT AUTO_INCREMENT PRIMARY KEY,
    product VARCHAR(50),
    category VARCHAR(50),
    amount DECIMAL(10, 2)
);

INSERT INTO sales (product, category, amount) VALUES
('ProductA', 'Category1', 100),
('ProductB', 'Category1', 150),
('ProductA', 'Category2', 200),
('ProductB', 'Category2', 250);

SELECT 
    category,
    SUM(CASE WHEN product = 'ProductA' THEN amount ELSE 0 END) AS ProductA,
    SUM(CASE WHEN product = 'ProductB' THEN amount ELSE 0 END) AS ProductB
FROM 
    sales
GROUP BY 
    category;

DELIMITER //

CREATE PROCEDURE DynamicPivot()
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE product VARCHAR(50);
    DECLARE cur CURSOR FOR SELECT DISTINCT product FROM sales;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    OPEN cur;

    read_loop: LOOP
        FETCH cur INTO product;
        IF done THEN
            LEAVE read_loop;
        END IF;

        SET @sql = CONCAT('ALTER TABLE temp ADD COLUMN ', product, ' DECIMAL(10, 2)');
        PREPARE stmt FROM @sql;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;

        SET @sql = CONCAT('UPDATE temp SET ', product, ' = (SELECT SUM(amount) FROM sales WHERE product = ''', product, ''')');
        PREPARE stmt FROM @sql;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
    END LOOP;

    CLOSE cur;
END //

DELIMITER ;