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...