mysql表转置

CREATE TABLE sales (
    product VARCHAR(50),
    region VARCHAR(50),
    amount INT
);

INSERT INTO sales (product, region, amount) VALUES
('ProductA', 'Region1', 100),
('ProductA', 'Region2', 200),
('ProductB', 'Region1', 150),
('ProductB', 'Region2', 250);

SELECT
    GROUP_CONCAT(DISTINCT region) AS regions,
    SUM(CASE WHEN region = 'Region1' THEN amount ELSE 0 END) AS Region1,
    SUM(CASE WHEN region = 'Region2' THEN amount ELSE 0 END) AS Region2
FROM sales
GROUP BY product;

DELIMITER //

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

    DROP TEMPORARY TABLE IF EXISTS temp_transpose;
    CREATE TEMPORARY TABLE temp_transpose (
        product VARCHAR(50),
        Region1 INT,
        Region2 INT
    );

    OPEN cur;

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

        UPDATE temp_transpose
        SET
            Region1 = IF(region = 'Region1', amount, Region1),
            Region2 = IF(region = 'Region2', amount, Region2)
        WHERE product = product;

        IF ROW_COUNT() = 0 THEN
            INSERT INTO temp_transpose (product, Region1, Region2)
            VALUES (product, IF(region = 'Region1', amount, 0), IF(region = 'Region2', amount, 0));
        END IF;
    END LOOP;

    CLOSE cur;

    SELECT * FROM temp_transpose;
END //

DELIMITER ;

CALL DynamicTranspose();