Home Forums SQL Server 2008 T-SQL (SS2K8) Recusion on Group and their subGroups- how can I solve this problem? RE: Recusion on Group and their subGroups- how can I solve this problem?

  • January 3, 2012 at 6:06 am

    #1427675

    Try this query, using different values of Groupname. If it works, then we'll pivot the results using FOR XML PATH to put them into a comma-delimited list:

    ;WITH rCTE AS (

    SELECT Level = 1, Id, GroupId, GroupName, ParentGroupId, ParentGroupName

    FROM dbo.groupsAssociation

    WHERE [GroupName] IN ('A') --('A','C','D')

    UNION ALL

    SELECT Level = Level + 1, tr.Id, tr.GroupId, tr.GroupName, tr.ParentGroupId, tr.ParentGroupName

    FROM dbo.groupsAssociation tr

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

    WHERE lr.Level < 8

    )

    SELECT GroupId, GroupName, ParentGroupId, ParentGroupName

    FROM rCTE

    GROUP BY GroupId, GroupName

    ORDER BY GroupName

    [font="Arial"]Low-hanging fruit picker and defender of the moggies[/font]

    For better assistance in answering your questions, please read this[/url].

    Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

    Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]