Hi Craig

I've had a go with your example data using the following and it appears to do what you require ... well the removing of empty tiers

I hope it works for you and I haven't messed up your requirement

;with rcte as (

select ParentId ID, null ParentID, 1 HLevel

,ParentID RootID

,null tier1ID

,null tier2ID

,null tier3ID

,null tier4ID

,null tier5ID

from #RecursiveGroupFinding gp

where not exists (select 1 from #RecursiveGroupFinding gc where gp.parentid = gc.childid)

group by gp.Parentid

union all

select gc.ChildID, r.id, HLevel + 1

,RootID RootID

,case when hlevel = 1 then gc.ChildID else tier1ID end tier1ID

,case when hlevel = 2 then gc.ChildID else tier2ID end tier2ID

,case when hlevel = 3 then gc.ChildID else tier3ID end tier3ID

,case when hlevel = 4 then gc.ChildID else tier4ID end tier4ID

,case when hlevel = 5 then gc.ChildID else tier5ID end tier5ID

from #RecursiveGroupFinding gc

inner join rcte r on r.id = gc.parentid

)

select Hlevel [tag], Case when HLevel - 1 = 0 then null else HLevel - 1 end [parent]

,case when Hlevel = 1 then GroupName else null end [RootLevel!1!GroupName]

,case when Hlevel = 2 then GroupName else null end [Tier1!2!T1GroupName]

,case when Hlevel = 3 then GroupName else null end [Tier2!3!T2GroupName]

,case when Hlevel = 4 then GroupName else null end [Tier3!4!T3GroupName]

,case when Hlevel = 5 then GroupName else null end [Tier4!5!T4GroupName]

,case when Hlevel = 5 then GroupName else null end [Tier5!6!T5GroupName]

from rcte r

inner join #GroupList g on r.id = g.groupid

order by rootid, tier1ID, tier2ID, tier3ID, tier4ID, tier5ID

for xml explicit

Results with

<RootLevel GroupName="Parent1">

<Tier1 T1GroupName="Child1">

<Tier2 T2GroupName="SubChild1">

<Tier3 T3GroupName="Icing" />

</Tier2>

<Tier2 T2GroupName="Subchild2">

<Tier3 T3GroupName="Icing" />

</Tier2>

</Tier1>

<Tier1 T1GroupName="Child2">

<Tier2 T2GroupName="Subchild3" />

</Tier1>

</RootLevel>

<RootLevel GroupName="Parent2">

<Tier1 T1GroupName="Child1">

<Tier2 T2GroupName="SubChild1">

<Tier3 T3GroupName="Icing" />

</Tier2>

<Tier2 T2GroupName="Subchild2">

<Tier3 T3GroupName="Icing" />

</Tier2>

</Tier1>

<Tier1 T1GroupName="Child3" />

</RootLevel>