;WITH Groups AS (

SELECT GroupId = g.Id, g.GroupName, ga.ParentGroupId

FROM dbo.[group] g

LEFT JOIN GroupAssociations ga on g.Id = ga.GroupId

)

, rCTE AS (

SELECT [Level] = 1, GroupId, GroupName, ParentGroupId -- anchor part

FROM Groups

WHERE ParentGroupId IS NULL

UNION ALL

SELECT [Level] = [Level] + 1, tr.GroupId, tr.GroupName, tr.ParentGroupId -- recursive part

FROM Groups tr

INNER JOIN rCTE lr ON lr.GroupId = tr.ParentGroupId

WHERE lr.[Level] < 8

)

SELECT *

FROM rCTE

The rCTE works like this:

The anchor part - the first select - picks up all rows which are top-level i.e. have no parent.

WHERE ParentGroupId IS NULL

The first iteration of the recursive part picks up rows which have, as their parent, rows from the anchor.

lr.GroupId = tr.ParentGroupId (lastrow.GroupId = thisrow.ParentGroupId)

The second iteration picks up their "children" and so on.

On each iteration of the recursive part, rCTE lr contains the results of the last iteration - which is the anchor set of rows for the first iteration.