Oracle 21c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE transaction (TRANS_VALUE, USER_ID, TRANS_TYPE_ID) AS
SELECT 10, 1, 2 FROM DUAL UNION ALL
SELECT  5, 2, 1 FROM DUAL UNION ALL
SELECT 15, 1, 1 FROM DUAL UNION ALL
SELECT 20, 2, 2 FROM DUAL UNION ALL
SELECT 10, 1, 2 FROM DUAL UNION ALL
SELECT  5, 1, 2 FROM DUAL UNION ALL
SELECT 15, 3, 1 FROM DUAL UNION ALL
SELECT 20, 3, 1 FROM DUAL;
8 rows affected

SELECT user_id,
       SUM(CASE trans_type_id WHEN 1 THEN trans_value END) AS sum_trans_type_1,
       SUM(CASE trans_type_id WHEN 2 THEN trans_value END) AS sum_trans_type_2
FROM   transaction
GROUP BY user_id


USER_ID
SUM_TRANS_TYPE_1
SUM_TRANS_TYPE_2


1
15
25

2
5
20

3
35
null


SELECT *
FROM   transaction
PIVOT (
  SUM(trans_value)
  FOR trans_type_id IN (
    1 AS sum_trans_type_1,
    2 AS sum_trans_type_2
  )
)


USER_ID
SUM_TRANS_TYPE_1
SUM_TRANS_TYPE_2


1
15
25

2
5
20

3
35
null