I'm trying to understand your code ...

In this example your first select statement

SELECT

[Level] = 1,

tr.ParentItemID, tr.ChildItemID,

List = CAST(CAST(tr.ParentItemID AS VARCHAR(10)) + '>' + CAST(tr.ChildItemID AS VARCHAR(10)) AS VARCHAR(8000))

FROM #tblItemRelationship tr

WHERE ParentItemID = 1

selects one row from #tblItemRelationship table which matches the parentID we're interested in and aliases this as 'tr'

Your second select ...

SELECT

[Level] = lr.[Level] + 1,

tr.ParentItemID, tr.ChildItemID,

List = CAST(lr.List + '>' + CAST(tr.ChildItemID AS VARCHAR(10)) AS VARCHAR(8000))

FROM rCTE lr

INNER JOIN #tblItemRelationship tr ON tr.ParentItemID = lr.ChildItemID

effectively joins #tblItemRelationship to itself - to the alias 'tr'

But 'tr' only contains one row - I don't understand how/why the derived(?) table rCTE manages to seem to do multiple joins to itself ... and the syntax - there is a 'with rCTE' statement that seems to enclose all the code, and then rCTE is aliased as 'lr' ... whole thing baffles me and I'd really like to understand. I wonder if you could explain please.

Thanks again.