mysql 行转列sql语句

CREATE TABLE sales (
    id INT AUTO_INCREMENT PRIMARY KEY,
    product VARCHAR(50),
    sales_amount INT,
    sale_date DATE
);

SELECT 
    sale_date,
    SUM(CASE WHEN product = 'ProductA' THEN sales_amount ELSE 0 END) AS ProductA,
    SUM(CASE WHEN product = 'ProductB' THEN sales_amount ELSE 0 END) AS ProductB,
    SUM(CASE WHEN product = 'ProductC' THEN sales_amount ELSE 0 END) AS ProductC
FROM 
    sales
GROUP BY 
    sale_date;

SELECT 
    sale_date,
    ProductA,
    ProductB,
    ProductC
FROM (
    SELECT 
        sale_date,
        product,
        sales_amount
    FROM 
        sales
) AS source_table
PIVOT(
    SUM(sales_amount)
    FOR product IN ('ProductA', 'ProductB', 'ProductC')
) AS pivot_table;

SET @columns = (SELECT GROUP_CONCAT(DISTINCT product) FROM sales);
SET @sql = CONCAT('SELECT sale_date, ', @columns, ' FROM (SELECT sale_date, product, sales_amount FROM sales) AS source_table PIVOT(SUM(sales_amount) FOR product IN (', @columns, ')) AS pivot_table');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;