SQL列名是数据，取消透视无效

CREATE TABLE sales (
    product VARCHAR(50),
    category VARCHAR(50),
    amount INT
);

INSERT INTO sales (product, category, amount) VALUES
('ProductA', 'Category1', 100),
('ProductB', 'Category2', 200),
('ProductA', 'Category2', 150),
('ProductB', 'Category1', 300);

SELECT product,
       SUM(CASE WHEN category = 'Category1' THEN amount ELSE 0 END) AS Category1,
       SUM(CASE WHEN category = 'Category2' THEN amount ELSE 0 END) AS Category2
FROM sales
GROUP BY product;

SELECT product, 'Category1' AS category, Category1 AS amount FROM (
    SELECT product,
           SUM(CASE WHEN category = 'Category1' THEN amount ELSE 0 END) AS Category1
    FROM sales
    GROUP BY product
) AS pivot_table
UNION ALL
SELECT product, 'Category2' AS category, Category2 AS amount FROM (
    SELECT product,
           SUM(CASE WHEN category = 'Category2' THEN amount ELSE 0 END) AS Category2
    FROM sales
    GROUP BY product
) AS pivot_table;