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


select identifier,
  allocationpercentage,
  marketvalueheld,
  allocationassettype
from is_id
union all
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
ALLOCATIONPERCENTAGE
MARKETVALUEHELD
ALLOCATIONASSETTYPE


IE0031069382
8.0426
9982313.29
Common Shares

IE0031069382
7.186
8919089.77
Common Shares

IE0031069382
5.0382
6253318.14
Common Shares

IE0031069382
4.8923
6072222.9
Common Shares

IE0031069382
4.273
5303577.93
Common Shares

IE0031069382
3.6568
4538714.24
Common Shares

IE0031069382
2.564
3182350.48
Common Shares

IE0031069382
2.4302
3016369
Common Shares

IE0031069382
2.3514
2918447.76
Common Shares

IE0031069382
2.3046
2860398.55
Common Shares

IE0031069382
.1833
227450.88
Cash

IE0031069382
.1812
224980.4
Cash

IE0031069382
.0806
100089.39
Cash

LU0138075311
5.8697
7264193.39
Common Shares

LU0138075311
5.6834
7033686.92
Common Shares

LU0138075311
5.4946
6800033.58
Common Shares

LU0138075311
5.4077
6692525.6
Common Shares

LU0138075311
4.9289
6099902.95
Common Shares

LU0138075311
4.5888
5678999.49
Common Shares

LU0138075311
4.567
5652088.55
Common Shares

LU0138075311
4.3649
5401906.25
Common Shares

LU0138075311
.1517
187726.9
Cash

LU0138075311
.0444
54982.42
Cash

LU0138075311
0
-.68
Cash

LU0138075311
.1961
242708.64
Cash

IE0031069382
.4451
552520.67
Cash

LU0138075311
58.8989
null
Other

IE0031069382
56.8158
null
Other


select identifier,
  allocationpercentage,
  marketvalueheld,
  allocationassettype
from is_id
union all
select identifier,
  sum(allocationpercentage),
  sum(marketvalueheld),
  'Cash total'
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
ALLOCATIONPERCENTAGE
MARKETVALUEHELD
ALLOCATIONASSETTYPE


IE0031069382
8.0426
9982313.29
Common Shares

IE0031069382
7.186
8919089.77
Common Shares

IE0031069382
5.0382
6253318.14
Common Shares

IE0031069382
4.8923
6072222.9
Common Shares

IE0031069382
4.273
5303577.93
Common Shares

IE0031069382
3.6568
4538714.24
Common Shares

IE0031069382
2.564
3182350.48
Common Shares

IE0031069382
2.4302
3016369
Common Shares

IE0031069382
2.3514
2918447.76
Common Shares

IE0031069382
2.3046
2860398.55
Common Shares

IE0031069382
.1833
227450.88
Cash

IE0031069382
.1812
224980.4
Cash

IE0031069382
.0806
100089.39
Cash

LU0138075311
5.8697
7264193.39
Common Shares

LU0138075311
5.6834
7033686.92
Common Shares

LU0138075311
5.4946
6800033.58
Common Shares

LU0138075311
5.4077
6692525.6
Common Shares

LU0138075311
4.9289
6099902.95
Common Shares

LU0138075311
4.5888
5678999.49
Common Shares

LU0138075311
4.567
5652088.55
Common Shares

LU0138075311
4.3649
5401906.25
Common Shares

LU0138075311
.1517
187726.9
Cash

LU0138075311
.0444
54982.42
Cash

LU0138075311
0
-.68
Cash

LU0138075311
.1961
242708.64
Cash total

IE0031069382
.4451
552520.67
Cash total

LU0138075311
58.8989
null
Other

IE0031069382
56.8158
null
Other


select identifier,
  allocationpercentage,
  marketvalueheld,
  allocationassettype,
  1 as data_type
from is_id
union all
select identifier,
  sum(allocationpercentage),
  sum(marketvalueheld),
  'Cash',
  2 as data_type
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',
  3 as data_type
from is_id
group by identifier
order by identifier, data_type;


IDENTIFIER
ALLOCATIONPERCENTAGE
MARKETVALUEHELD
ALLOCATIONASSETTYPE
DATA_TYPE


IE0031069382
2.3046
2860398.55
Common Shares
1

IE0031069382
.0806
100089.39
Cash
1

IE0031069382
8.0426
9982313.29
Common Shares
1

IE0031069382
2.3514
2918447.76
Common Shares
1

IE0031069382
2.4302
3016369
Common Shares
1

IE0031069382
2.564
3182350.48
Common Shares
1

IE0031069382
3.6568
4538714.24
Common Shares
1

IE0031069382
4.273
5303577.93
Common Shares
1

IE0031069382
4.8923
6072222.9
Common Shares
1

IE0031069382
5.0382
6253318.14
Common Shares
1

IE0031069382
7.186
8919089.77
Common Shares
1

IE0031069382
.1833
227450.88
Cash
1

IE0031069382
.1812
224980.4
Cash
1

IE0031069382
.4451
552520.67
Cash
2

IE0031069382
56.8158
null
Other
3

LU0138075311
0
-.68
Cash
1

LU0138075311
.0444
54982.42
Cash
1

LU0138075311
.1517
187726.9
Cash
1

LU0138075311
4.3649
5401906.25
Common Shares
1

LU0138075311
4.567
5652088.55
Common Shares
1

LU0138075311
4.5888
5678999.49
Common Shares
1

LU0138075311
4.9289
6099902.95
Common Shares
1

LU0138075311
5.4077
6692525.6
Common Shares
1

LU0138075311
5.4946
6800033.58
Common Shares
1

LU0138075311
5.6834
7033686.92
Common Shares
1

LU0138075311
5.8697
7264193.39
Common Shares
1

LU0138075311
.1961
242708.64
Cash
2

LU0138075311
58.8989
null
Other
3


select identifier,
  allocationpercentage,
  marketvalueheld,
  allocationassettype,
  1 as data_type
from is_id
union all
select identifier,
  sum(allocationpercentage),
  sum(marketvalueheld),
  'Cash total',
  2 as data_type
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 total',
  3 as data_type
from is_id
group by identifier
order by identifier, data_type;


IDENTIFIER
ALLOCATIONPERCENTAGE
MARKETVALUEHELD
ALLOCATIONASSETTYPE
DATA_TYPE


IE0031069382
2.3046
2860398.55
Common Shares
1

IE0031069382
.0806
100089.39
Cash
1

IE0031069382
8.0426
9982313.29
Common Shares
1

IE0031069382
2.3514
2918447.76
Common Shares
1

IE0031069382
2.4302
3016369
Common Shares
1

IE0031069382
2.564
3182350.48
Common Shares
1

IE0031069382
3.6568
4538714.24
Common Shares
1

IE0031069382
4.273
5303577.93
Common Shares
1

IE0031069382
4.8923
6072222.9
Common Shares
1

IE0031069382
5.0382
6253318.14
Common Shares
1

IE0031069382
7.186
8919089.77
Common Shares
1

IE0031069382
.1833
227450.88
Cash
1

IE0031069382
.1812
224980.4
Cash
1

IE0031069382
.4451
552520.67
Cash total
2

IE0031069382
56.8158
null
Other total
3

LU0138075311
0
-.68
Cash
1

LU0138075311
.0444
54982.42
Cash
1

LU0138075311
.1517
187726.9
Cash
1

LU0138075311
4.3649
5401906.25
Common Shares
1

LU0138075311
4.567
5652088.55
Common Shares
1

LU0138075311
4.5888
5678999.49
Common Shares
1

LU0138075311
4.9289
6099902.95
Common Shares
1

LU0138075311
5.4077
6692525.6
Common Shares
1

LU0138075311
5.4946
6800033.58
Common Shares
1

LU0138075311
5.6834
7033686.92
Common Shares
1

LU0138075311
5.8697
7264193.39
Common Shares
1

LU0138075311
.1961
242708.64
Cash total
2

LU0138075311
58.8989
null
Other total
3
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
IDENTIFIER	ALLOCATIONPERCENTAGE	MARKETVALUEHELD	ALLOCATIONASSETTYPE
IE0031069382	8.0426	9982313.29	Common Shares
IE0031069382	7.186	8919089.77	Common Shares
IE0031069382	5.0382	6253318.14	Common Shares
IE0031069382	4.8923	6072222.9	Common Shares
IE0031069382	4.273	5303577.93	Common Shares
IE0031069382	3.6568	4538714.24	Common Shares
IE0031069382	2.564	3182350.48	Common Shares
IE0031069382	2.4302	3016369	Common Shares
IE0031069382	2.3514	2918447.76	Common Shares
IE0031069382	2.3046	2860398.55	Common Shares
IE0031069382	.1833	227450.88	Cash
IE0031069382	.1812	224980.4	Cash
IE0031069382	.0806	100089.39	Cash
LU0138075311	5.8697	7264193.39	Common Shares
LU0138075311	5.6834	7033686.92	Common Shares
LU0138075311	5.4946	6800033.58	Common Shares
LU0138075311	5.4077	6692525.6	Common Shares
LU0138075311	4.9289	6099902.95	Common Shares
LU0138075311	4.5888	5678999.49	Common Shares
LU0138075311	4.567	5652088.55	Common Shares
LU0138075311	4.3649	5401906.25	Common Shares
LU0138075311	.1517	187726.9	Cash
LU0138075311	.0444	54982.42	Cash
LU0138075311	0	-.68	Cash
LU0138075311	.1961	242708.64	Cash
IE0031069382	.4451	552520.67	Cash
LU0138075311	58.8989	null	Other
IE0031069382	56.8158	null	Other