create table packagedetails
(package_id int ,Package_name varchar(20),[Level] int,Description varchar(30),Main_package_id int)
insert into packagedetails values
(5,'Project 1A', 2,'Project 1B', NULL),
(6, 'PKG-1', 3, 'Package 1',5),
(7, 'PKG-2', 3,' Package 2',5),
(8, 'PKG-3', 3 ,'Package 3', 13),
(9, 'PKG-4', 3, 'Package 4', 13),
(13, 'Project 1B', 2, 'Project 2B', NULL ),
(14, 'Project 2', 2, 'Project 2', NULL ),
(12, 'PKG-7', 3 ,'Package 7', 14)
with cte as
(select packagedetails.*,
case when Main_package_id is null then package_id else Main_package_id end as newpackageid
,1 as Lvl From packagedetails
where Main_package_id is null
union all
select packagedetails.*,newpackageid,cte.Lvl -1 as Lvls
from packagedetails
join cte on packagedetails.Main_package_id =cte.package_id
where packagedetails.Main_package_id is not null)
select package_id,package_name,level,description,
row_number() over (partition by newpackageid order by lvl desc) as row
From cte