mysql字段行转列

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

INSERT INTO sales (product, amount) VALUES
('ProductA', 100),
('ProductB', 200),
('ProductA', 150),
('ProductC', 300),
('ProductB', 250);

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

CREATE TABLE user_actions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT,
    action VARCHAR(50),
    count INT
);

INSERT INTO user_actions (user_id, action, count) VALUES
(1, 'click', 10),
(1, 'view', 20),
(2, 'click', 15),
(2, 'view', 25),
(2, 'purchase', 1);

SET @sql = NULL;
SELECT
    GROUP_CONCAT(DISTINCT CONCAT('SUM(IF(action = ''', action, ''', count, 0)) AS ', action))
INTO @sql
FROM user_actions;

SET @sql = CONCAT('SELECT user_id, ', @sql, ' FROM user_actions GROUP BY user_id');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;