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),
('ProductC', 'Category2', 200),
('ProductD', '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,
    SUM(CASE WHEN product = 'ProductC' THEN amount ELSE 0 END) AS ProductC,
    SUM(CASE WHEN product = 'ProductD' THEN amount ELSE 0 END) AS ProductD
FROM sales
GROUP BY category;

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

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

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