mysql 行列转置

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

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

SELECT 
    Region1,
    Region2
FROM (
    SELECT 
        region,
        product,
        sales_amount
    FROM sales
) AS s
PIVOT (
    SUM(sales_amount)
    FOR region IN ('Region1', 'Region2')
) AS p;

SET @sql = NULL;
SELECT
    GROUP_CONCAT(DISTINCT CONCAT('MAX(IF(region = ''', region, ''', sales_amount, NULL)) AS ', region))
INTO @sql
FROM sales;

SET @sql = CONCAT('SELECT product, ', @sql, ' FROM (SELECT product, region, sales_amount FROM sales) AS s GROUP BY product');

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

SELECT 
    Region1,
    Region2
FROM (
    SELECT 
        region,
        product,
        COALESCE(sales_amount, 0) AS sales_amount
    FROM sales
) AS s
PIVOT (
    SUM(sales_amount)
    FOR region IN ('Region1', 'Region2')
) AS p;