mysql 把行变成列

SELECT 
    user_id,
    SUM(CASE WHEN category = 'A' THEN amount ELSE 0 END) AS 'A',
    SUM(CASE WHEN category = 'B' THEN amount ELSE 0 END) AS 'B',
    SUM(CASE WHEN category = 'C' THEN amount ELSE 0 END) AS 'C'
FROM transactions
GROUP BY user_id;

SELECT 
    user_id,
    (
        SELECT GROUP_CONCAT(amount ORDER BY transaction_date SEPARATOR ', ')
        FROM transactions t2
        WHERE t2.user_id = t1.user_id AND t2.category = 'A'
    ) AS 'A',
    (
        SELECT GROUP_CONCAT(amount ORDER BY transaction_date SEPARATOR ', ')
        FROM transactions t3
        WHERE t3.user_id = t1.user_id AND t3.category = 'B'
    ) AS 'B'
FROM transactions t1
GROUP BY user_id;

SELECT 
    user_id,
    'A' AS category,
    A.amount AS amount
FROM (
    SELECT user_id, 'A' AS category, amount
    FROM transactions
    WHERE category = 'A'
    UNION ALL
    SELECT user_id, 'B' AS category, NULL
    FROM transactions
    WHERE category != 'A'
) AS A
GROUP BY user_id, category
HAVING category = 'A'
UNION ALL
SELECT 
    user_id,
    'B' AS category,
    B.amount AS amount
FROM (
    SELECT user_id, 'A' AS category, amount
    FROM transactions
    WHERE category = 'B'
    UNION ALL
    SELECT user_id, 'B' AS category, NULL
    FROM transactions
    WHERE category != 'B'
) AS B
GROUP BY user_id, category
HAVING category = 'B';