Hierarchy Level

Hi

i tried on CTE but iam not gettng correct result plz help me


Declare @TMaster Table (MasterId int, Type varchar(100), ParentMasterId int)
Insert into @Tmaster
Select 1, 'Fixed', 14 Union All
Select 2, 'Flexible',14 union All
Select 3, 'Others',14 union All
Select 13, 'Retirement benefits',1 union All
Select 14, 'PBHeads', 0

Select * From @Tmaster

Declare @TDetails Table ( MasterId Int, Description varchar(100))
Insert into @TDetails
Select 1, 'Basic' union all
Select 1,'Conveyance' union all
Select 1,'HRA' union all
Select 1,'Special Allowance' union all
Select 2, 'Children Education' union all
Select 2,'Travel Allowance' union all
Select 2,'Medical Expenses' union all
Select 2,'Variable Spl Pay' union all
Select 3, 'Project Allowance' union all
Select 3,'Quarterly Incentive' union all
Select 3,'Shift Allowance' union all
Select 3,'Annual Component' union all
Select 13,'Provident Fund' union all
Select 13,'Gratuity' union all
Select 13,'Super annuation'


Select * From @TDetails

in group list we have to show fixed, flexible, other
in subgroups(L1) list under fixed we have to show
Basic salary, HRA, Conveyance, Special Allowance,Retirement benefits

in subgroup(L2) we have to show Gratuity, providedfund, Super annuation they are subgroups of retirementbenefits


output :
Group Subgroup(L1) Subgroup(L2)
Fixed
Basic salary
HRA
Conveyance
Special Allowance
Retirement benefits
Provident Fund
Gratuity
Super annuation

Flexible reimbursements
LTA/Group Travel
Medical expenses
Children education
variable special pay

Others
Project allowance
Performance incentive
Medical insurance