mysql 行列转换

CREATE TABLE sales (
    id INT PRIMARY KEY,
    product VARCHAR(50),
    month VARCHAR(10),
    amount DECIMAL(10, 2)
);

SELECT product,
       GROUP_CONCAT(month, ':', amount SEPARATOR '; ') AS monthly_sales
FROM sales
GROUP BY product;

CREATE TABLE summary (
    id INT PRIMARY KEY,
    product VARCHAR(50),
    jan_amount DECIMAL(10, 2),
    feb_amount DECIMAL(10, 2),
    mar_amount DECIMAL(10, 2)
);

SELECT product,
       'Jan' AS month,
       jan_amount AS amount
FROM summary
UNION ALL
SELECT product,
       'Feb' AS month,
       feb_amount AS amount
FROM summary
UNION ALL
SELECT product,
       'Mar' AS month,
       mar_amount AS amount
FROM summary;

SELECT product,
       GROUP_CONCAT(DISTINCT month, ':', amount SEPARATOR '; ') AS monthly_sales
FROM sales
GROUP BY product;

SELECT product,
       'Jan' AS month,
       CAST(jan_amount AS DECIMAL(10, 2)) AS amount
FROM summary
UNION ALL
SELECT product,
       'Feb' AS month,
       CAST(feb_amount AS DECIMAL(10, 2)) AS amount
FROM summary
UNION ALL
SELECT product,
       'Mar' AS month,
       CAST(mar_amount AS DECIMAL(10, 2)) AS amount
FROM summary;