mysql sql行列转换

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', 'Region1', 100),
('ProductA', 'Region2', 200),
('ProductB', 'Region1', 150),
('ProductB', 'Region2', 250);

SELECT product,
       GROUP_CONCAT(region ORDER BY region SEPARATOR ', ') AS regions,
       SUM(amount) AS total_amount
FROM sales
GROUP BY product;

CREATE TABLE product_sales (
    product VARCHAR(50),
    region1_amount INT,
    region2_amount INT
);

INSERT INTO product_sales (product, region1_amount, region2_amount) VALUES
('ProductA', 100, 200),
('ProductB', 150, 250);

SELECT product, 'Region1' AS region, region1_amount AS amount FROM product_sales
UNION ALL
SELECT product, 'Region2' AS region, region2_amount AS amount FROM product_sales;

SELECT product,
       GROUP_CONCAT(DISTINCT region ORDER BY region SEPARATOR ', ') AS regions,
       SUM(amount) AS total_amount
FROM sales
GROUP BY product;

SELECT product, 'Region1' AS region, IFNULL(region1_amount, 0) AS amount FROM product_sales
UNION ALL
SELECT product, 'Region2' AS region, IFNULL(region2_amount, 0) AS amount FROM product_sales;