mysql将行列转换

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

INSERT INTO sales (product, month, amount) VALUES
('Product A', 'January', 100),
('Product A', 'February', 150),
('Product B', 'January', 200),
('Product B', 'February', 250);

SELECT product,
       MAX(CASE WHEN month = 'January' THEN amount END) AS January,
       MAX(CASE WHEN month = 'February' THEN amount END) AS February
FROM sales
GROUP BY product;

CREATE TABLE products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50),
    category VARCHAR(50)
);

INSERT INTO products (name, category) VALUES
('Product A', 'Category 1'),
('Product B', 'Category 1'),
('Product C', 'Category 2');

SELECT name AS Product, 'Category' AS Type, category AS Value
FROM products
UNION ALL
SELECT 'Total', 'Category', category
FROM products
GROUP BY category;

SELECT product,
       COALESCE(MAX(CASE WHEN month = 'January' THEN amount END), 0) AS January,
       COALESCE(MAX(CASE WHEN month = 'February' THEN amount END), 0) AS February
FROM sales
GROUP BY product;

SELECT DISTINCT product,
       MAX(CASE WHEN month = 'January' THEN amount END) AS January,
       MAX(CASE WHEN month = 'February' THEN amount END) AS February
FROM sales
GROUP BY product;