Declare @My_table as table
( [Opty ID] nvarchar (20) null,
[Product Category] nvarchar (50) null,
[Product] nvarchar (5) null,
[Value] float null)
insert into @My_table ( [Opty ID],[Product Category],[Product],[Value])
values
('A0004764376', ' ','WE',100),
('A0004764376',' ', 'UU', 200),
('A0004764376','Hyper', 'AE', 120),
('A0004764376','Voyo', 'G4', 300),
('A0004759217', ' ', 'WE', 200),
('A0004759217', 'Edge','7A', 400),
('A0004759217', 'Hyper','AE', 70)
;
select a.[Opty ID]
,bTable.[Product Category]
,a.Product
, a.Value * (bTable.Value/(
select sum(c.Value)
from @My_table c
where c.[Opty ID] = a.[Opty ID]
and Rtrim(c.[Product Category]) <> ''
)) [value]
from @My_table a
outer apply
(
select b.[Product Category] , b.Value
from @My_table b
where b.[Opty ID] = a.[Opty ID]
and Rtrim(b.[Product Category]) <> ''
) bTable
where Rtrim(a.[Product Category]) = ''
union all
select *
from @My_table a
where Rtrim(a.[Product Category]) <> ''
order by 1;
1 Like