mysql对部分列做转置

CREATE TABLE sales (
    id INT AUTO_INCREMENT PRIMARY KEY,
    product VARCHAR(50),
    region VARCHAR(50),
    amount INT
);

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

SELECT region,
       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 region;

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT CONCAT('SUM(IF(product = ''', product, ''', amount, 0)) AS ', product))
INTO @sql
FROM sales;

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

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