行列变换mysql

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

INSERT INTO sales (product, category, amount) VALUES
('Product A', 'Category 1', 100),
('Product B', 'Category 1', 200),
('Product C', 'Category 2', 150),
('Product D', 'Category 2', 300);

SELECT 
    category,
    MAX(CASE WHEN product = 'Product A' THEN amount ELSE 0 END) AS ProductA,
    MAX(CASE WHEN product = 'Product B' THEN amount ELSE 0 END) AS ProductB,
    MAX(CASE WHEN product = 'Product C' THEN amount ELSE 0 END) AS ProductC,
    MAX(CASE WHEN product = 'Product D' THEN amount ELSE 0 END) AS ProductD
FROM sales
GROUP BY category;

CREATE TABLE report (
    id INT AUTO_INCREMENT PRIMARY KEY,
    Q1 INT,
    Q2 INT,
    Q3 INT,
    Q4 INT
);

INSERT INTO report (Q1, Q2, Q3, Q4) VALUES (100, 200, 150, 300);

SELECT 
    'Q1' AS Quarter, Q1 AS Amount FROM report
UNION ALL
SELECT 
    'Q2' AS Quarter, Q2 AS Amount FROM report
UNION ALL
SELECT 
    'Q3' AS Quarter, Q3 AS Amount FROM report
UNION ALL
SELECT 
    'Q4' AS Quarter, Q4 AS Amount FROM report;