create table tab(

ItemId int,

ParentId int,

Name varchar(10),

QtyAvailable int);

insert into tab (

ItemId,

ParentId,

Name,

QtyAvailable)

select

1 as ItemId,

null as ParentId,

'Package A' as Name,

10 as QtyAvailable

union all

select

2,

1,

'Item 1',

2

union all

select

3,

1,

'Item 2',

3;

with

subquery_cte as (

select

coalesce(ParentId, ItemId) as Id,

case

when

ParentId is null

then

Name

else

null

end as Name,

sum(QtyAvailable) as QtyAvailable

from

tab

group by

coalesce(ParentId, ItemId),

case

when

ParentId is null

then

Name

else

null

end)

select

min(Name) as Name,

sum(QtyAvailable) as QtyAvailable

from

subquery_cte

group by

Id;

drop table tab;

Assuming, you mean simple parent-child relation, this is just a bit different solution. I believe it's rather efficient because references the table only once.