Postgres 12

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

create table your_table
(
  unique_key varchar(30) primary key,
  category varchar(20),
  date_period integer, 
  value integer
);
insert into your_table 
(unique_key, category, date_period, value) values 
  ('agriculture_all', 'agriculture', 2021, 15)
, ('agriculture_partial', 'agriculture', 2021, 10)
, ('science_all', 'science', 2021, 83)
, ('science_partial', 'science', 2021, 32)
;
4 rows affected

SELECT 
  t.category
, t.date_period
, SUM(CASE WHEN t.unique_key LIKE '%\_all' THEN t.value ELSE 0 END) AS "all"
, SUM(CASE WHEN t.unique_key LIKE '%\_partial' THEN t.value ELSE 0 END) AS "partial"
FROM your_table t
GROUP BY t.category, t.date_period
ORDER BY t.category, t.date_period


category
date_period
all
partial


agriculture
2021
15
10

science
2021
83
32