How to join grouped and filtered fields on table?

I have this dataset that I want to group by user_id and sum the values by category

id	user_id	category	value
0	1	1	10
1	1	1	20
2	1	2	10
3	1	2	30
4	1	3	10
5	2	3	40

And I want a select returning a table with one element for user_id with one field for each category value (These are known and can only be 1, 2 or 3)

user_id	category_1	category_2	category_3
1	30	40	10
2	0	0	40

The only solution I’ve found is:

select
  r.user_id as user_id,
  rt_c1.value as category_1_value,
  rt_c2.emissions_change_co2e as scope2_co2e_emissions,
  rt_c3.emissions_change_co2e as scope3_co2e_emissions
from tablename as r
join (select 
  rt.user_id
  rt.category as category,
  sum(rt.value) as value
from tablename as rt
group by rt.user_id, rt.category) as rt_c1
on rt_c1.user_id = r.user_id and rt_c1.category = 1
join (select 
  rt.user_id
  rt.category as category,
  sum(rt.value) as value
from tablename as rt
group by rt.user_id, rt.category) as rt_c2
on rt_c2.user_id = r.user_id and rt_c2.category = 2
join (select 
  rt.user_id
  rt.category as category,
  sum(rt.value) as value
from tablename as rt
group by rt.user_id, rt.category) as rt_c3
on rt_c3.user_id = r.user_id and rt_c3.category = 3

Answer

Perhaps a simple conditional aggregation

Example

Select user_id
      ,sum( case when category=1 then value else 0 end) as category_1
      ,sum( case when category=2 then value else 0 end) as category_2 
      ,sum( case when category=3 then value else 0 end) as category_3
 From  YourTable
 Group By user_id