Oracle 18c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE  
    IS_ID  
    (  
        IDENTIFIER VARCHAR2(50),  
        ALLOCATIONPERCENTAGE NUMBER(19,8),  
        MARKETVALUEHELD NUMBER(19,8),  
        ALLOCATIONASSETTYPE VARCHAR2(100)  
    );
begin
INSERT INTO IS_ID (IDENTIFIER, ALLOCATIONPERCENTAGE, MARKETVALUEHELD, ALLOCATIONASSETTYPE) VALUES ('IE0031069382', 8.0426, 9982313.29, 'Common Shares');  
INSERT INTO IS_ID (IDENTIFIER, ALLOCATIONPERCENTAGE, MARKETVALUEHELD, ALLOCATIONASSETTYPE) VALUES ('IE0031069382', 7.186, 8919089.77, 'Common Shares');  
INSERT INTO IS_ID (IDENTIFIER, ALLOCATIONPERCENTAGE, MARKETVALUEHELD, ALLOCATIONASSETTYPE) VALUES ('IE0031069382', 5.0382, 6253318.14, 'Common Shares');  
INSERT INTO IS_ID (IDENTIFIER, ALLOCATIONPERCENTAGE, MARKETVALUEHELD, ALLOCATIONASSETTYPE) VALUES ('IE0031069382', 4.8923, 6072222.9, 'Common Shares');  
INSERT INTO IS_ID (IDENTIFIER, ALLOCATIONPERCENTAGE, MARKETVALUEHELD, ALLOCATIONASSETTYPE) VALUES ('IE0031069382', 4.273, 5303577.93, 'Common Shares');  
INSERT INTO IS_ID (IDENTIFIER, ALLOCATIONPERCENTAGE, MARKETVALUEHELD, ALLOCATIONASSETTYPE) VALUES ('IE0031069382', 3.6568, 4538714.24, 'Common Shares');  
INSERT INTO IS_ID (IDENTIFIER, ALLOCATIONPERCENTAGE, MARKETVALUEHELD, ALLOCATIONASSETTYPE) VALUES ('IE0031069382', 2.564, 3182350.48, 'Common Shares');  
INSERT INTO IS_ID (IDENTIFIER, ALLOCATIONPERCENTAGE, MARKETVALUEHELD, ALLOCATIONASSETTYPE) VALUES ('IE0031069382', 2.4302, 3016369, 'Common Shares');  
INSERT INTO IS_ID (IDENTIFIER, ALLOCATIONPERCENTAGE, MARKETVALUEHELD, ALLOCATIONASSETTYPE) VALUES ('IE0031069382', 2.3514, 2918447.76, 'Common Shares');  
INSERT INTO IS_ID (IDENTIFIER, ALLOCATIONPERCENTAGE, MARKETVALUEHELD, ALLOCATIONASSETTYPE) VALUES ('IE0031069382', 2.3046, 2860398.55, 'Common Shares');  
INSERT INTO IS_ID (IDENTIFIER, ALLOCATIONPERCENTAGE, MARKETVALUEHELD, ALLOCATIONASSETTYPE) VALUES ('IE0031069382', 0.1833, 227450.88, 'Cash');  
INSERT INTO IS_ID (IDENTIFIER, ALLOCATIONPERCENTAGE, MARKETVALUEHELD, ALLOCATIONASSETTYPE) VALUES ('IE0031069382', 0.1812, 224980.4, 'Cash');  
INSERT INTO IS_ID (IDENTIFIER, ALLOCATIONPERCENTAGE, MARKETVALUEHELD, ALLOCATIONASSETTYPE) VALUES ('IE0031069382', 0.0806, 100089.39, 'Cash');  
INSERT INTO IS_ID (IDENTIFIER, ALLOCATIONPERCENTAGE, MARKETVALUEHELD, ALLOCATIONASSETTYPE) VALUES ('LU0138075311', 5.8697, 7264193.39, 'Common Shares');  
INSERT INTO IS_ID (IDENTIFIER, ALLOCATIONPERCENTAGE, MARKETVALUEHELD, ALLOCATIONASSETTYPE) VALUES ('LU0138075311', 5.6834, 7033686.92, 'Common Shares');  
INSERT INTO IS_ID (IDENTIFIER, ALLOCATIONPERCENTAGE, MARKETVALUEHELD, ALLOCATIONASSETTYPE) VALUES ('LU0138075311', 5.4946, 6800033.58, 'Common Shares');  
INSERT INTO IS_ID (IDENTIFIER, ALLOCATIONPERCENTAGE, MARKETVALUEHELD, ALLOCATIONASSETTYPE) VALUES ('LU0138075311', 5.4077, 6692525.6, 'Common Shares');  
INSERT INTO IS_ID (IDENTIFIER, ALLOCATIONPERCENTAGE, MARKETVALUEHELD, ALLOCATIONASSETTYPE) VALUES ('LU0138075311', 4.9289, 6099902.95, 'Common Shares');  
INSERT INTO IS_ID (IDENTIFIER, ALLOCATIONPERCENTAGE, MARKETVALUEHELD, ALLOCATIONASSETTYPE) VALUES ('LU0138075311', 4.5888, 5678999.49, 'Common Shares');  
INSERT INTO IS_ID (IDENTIFIER, ALLOCATIONPERCENTAGE, MARKETVALUEHELD, ALLOCATIONASSETTYPE) VALUES ('LU0138075311', 4.567, 5652088.55, 'Common Shares');  
INSERT INTO IS_ID (IDENTIFIER, ALLOCATIONPERCENTAGE, MARKETVALUEHELD, ALLOCATIONASSETTYPE) VALUES ('LU0138075311', 4.3649, 5401906.25, 'Common Shares');  
INSERT INTO IS_ID (IDENTIFIER, ALLOCATIONPERCENTAGE, MARKETVALUEHELD, ALLOCATIONASSETTYPE) VALUES ('LU0138075311', 0.1517, 187726.9, 'Cash');  
INSERT INTO IS_ID (IDENTIFIER, ALLOCATIONPERCENTAGE, MARKETVALUEHELD, ALLOCATIONASSETTYPE) VALUES ('LU0138075311', 0.0444, 54982.42, 'Cash');  
INSERT INTO IS_ID (IDENTIFIER, ALLOCATIONPERCENTAGE, MARKETVALUEHELD, ALLOCATIONASSETTYPE) VALUES ('LU0138075311', 0, -0.68, 'Cash');  
end;
/
1 rows affected

select identifier,
  sum(allocationpercentage),
  sum(marketvalueheld),
  'Cash'
from is_id
where allocationassettype = 'Cash'
group by identifier
union all
select identifier,
  100
    - sum(case when allocationassettype != 'Cash' then allocationpercentage else 0 end)
    - sum(case when allocationassettype = 'Cash' then allocationpercentage else 0 end),
  null,
  'Other'
from is_id
group by identifier;


IDENTIFIER
SUM(ALLOCATIONPERCENTAGE)
SUM(MARKETVALUEHELD)
'CASH'


LU0138075311
.1961
242708.64
Cash

IE0031069382
.4451
552520.67
Cash

LU0138075311
58.8989
null
Other

IE0031069382
56.8158
null
Other


create view your_view (identifier, allocationpercentage, marketvalueheld, allocationassettype)
as
select identifier,
  sum(allocationpercentage),
  sum(marketvalueheld),
  'Cash'
from is_id
where allocationassettype = 'Cash'
group by identifier
union all
select identifier,
  100
    - sum(case when allocationassettype != 'Cash' then allocationpercentage else 0 end)
    - sum(case when allocationassettype = 'Cash' then allocationpercentage else 0 end),
  null,
  'Other'
from is_id
group by identifier;
select * from your_view;


IDENTIFIER
ALLOCATIONPERCENTAGE
MARKETVALUEHELD
ALLOCATIONASSETTYPE


LU0138075311
.1961
242708.64
Cash

IE0031069382
.4451
552520.67
Cash

LU0138075311
58.8989
null
Other

IE0031069382
56.8158
null
Other
IDENTIFIER	SUM(ALLOCATIONPERCENTAGE)	SUM(MARKETVALUEHELD)	'CASH'
LU0138075311	.1961	242708.64	Cash
IE0031069382	.4451	552520.67	Cash
LU0138075311	58.8989	null	Other
IE0031069382	56.8158	null	Other