Sql server query to flattening a hierarchy of records

Question

I have a table that describes a hierarchy:

Name    MemberName
A       B
A       C
B       D
D       E
F       G

MemberName references the Name column of the same table. From this table, I can easily query to see that B and C are members within A, D is a member of B, E is a member of D and G is a member of F.

Based on this structure it's difficult to write a query that shows that D, and E are also indirectly a member of A. D and E are also indirectly a member of B, etc. So what I need to do is build up a new table that shows shows all the indirect members. So for the above table data, I'd end up with a table containing:

Name    MemberName
A       B
A       C
A       D
A       E
B       D
B       E
D       E
F       G

I started by putting all the records that weren't members of other records (top level) records) into a temp table:

CREATE TABLE #TMP
(
    [Name] varchar(20),
    [MemberName] varchar(20)
)

DECLARE @iRowsFound INT
INSERT INTO #TMP ([Name],[MemberName]) 
(SELECT * FROM [HierarchyData] WHERE [Name] NOT IN 
   (SELECT [MemberName] FROM [HierarchyData]))
SELECT @iRowsFound = @@ROWCOUNT

Name    MemberName
A       B
A       C
F       G

Then my theory was to, in a while loop, cross join the temp table to the heirachy table and insert the applicable records from the cross join back into the temp table, and perform that while loop until there were no more applicable records in the cross join to insert:

WHILE (@iRowsFound > 0)
BEGIN
    INSERT INTO #TMP ([Name],[MemberName]) 
    (
        SELECT 
            [NewName] = ??,
            [NewMember] = ??
        FROM
            [HierarchyData],[#TMP]
        WHERE
            ???        
    )
    SELECT @iRowsFound = @@ROWCOUNT
END

I'm just not sure I'm on the right track, as I'm a little stumped as to what the cross join select should look like. Has anyone done something like this (in sql server 2000)?

Edit: I think I may have gotten it: - Although I'm pretty sure there must be a more efficient way to do this...

WHILE (@iRowsFound > 0)
BEGIN
    INSERT INTO #TMP ([Name],[MemberName]) 
    (       
            SELECT
                --[#TMP].[Name],
                --[#TMP].[MemberName],
                [HierarchyData].[Name],
                [HierarchyData].[MemberName]
            FROM 
                [#TMP]
            JOIN 
                [HierarchyData] ON [#TMP].[MemberName] = [HierarchyData].[Name]
            --WHERE
            --  [#TMP].[MemberName] = [HierarchyData].[Name]
            AND NOT EXISTS (SELECT * FROM [#TMP] WHERE [#TMP].[Name] = [HierarchyData].[Name] AND [#TMP].[MemberName] = [HierarchyData].[MemberName])   
            UNION   
            SELECT
                [#TMP].[Name],
                --[#TMP].[MemberName],
                --[HierarchyData].[Name],
                [HierarchyData].[MemberName]
            FROM 
                [#TMP]
            JOIN 
                [HierarchyData] ON [#TMP].[MemberName] = [HierarchyData].[Name]     
            AND NOT EXISTS (SELECT * FROM [#TMP] WHERE [#TMP].[Name] = [#TMP].[Name] AND [#TMP].[MemberName] = [HierarchyData].[MemberName])    

    )
    SELECT @iRowsFound = @@ROWCOUNT
END

Answer 1

Here is an SQL 2000 version.

Some notes: This will work with any numbers of levels and will not have cycle errors (like the CTE versions will.)

declare @lastcount int
declare @lastcycle int

Select HierarchyData.Name, HierarchyData.MemberName, 0 as [Cycle] INTO #list
FROM HierarchyData

SET @lastcount = @@rowcount
SET @lastcycle = 0

while @lastcount > 0
BEGIN
  INSERT INTO #list
    SELECT Members.Name, Child.MemberName as [MemberName], @lastcycle+1 as [Cycle]
    FROM #list Members
    JOIN HierarchyData Child ON Members.MemberName = Child.Name
    LEFT JOIN #list cycletest ON Members.Name = cycletest.Name AND Child.MemberName = cycletest.Membername
    WHERE Members.Cycle = @lastcycle AND NOT (Members.Name = Child.MemberName) AND cycletest.Name is null

  SET @lastcount = @@rowcount

  SET @lastcycle = @lastcycle + 1
END

SELECT [Name], [MemberName] FROM #list
ORDER BY [Name], [MemberName]

DROP TABLE #list

---- Test data
--create table HierarchyData
--(
--  [Name] varchar(20),
--  [MemberName] varchar(20)
--)
--
--INSERT INTO HierarchyData (Name,MemberName) Values('A','B')
--INSERT INTO HierarchyData (Name,MemberName) Values('A','C')
--INSERT INTO HierarchyData (Name,MemberName) Values('B','D')
--INSERT INTO HierarchyData (Name,MemberName) Values('D','E')
--INSERT INTO HierarchyData (Name,MemberName) Values('F','G')
----CYCLE TEST  (the CTE will not work)
--INSERT INTO HierarchyData (Name,MemberName) Values('E','D')
--
---- Test
--select * from HierarchyData

---- CTE Works (note, will fail on cycles.)
--WITH Members AS
--(
--  Select HierarchyData.Name, HierarchyData.MemberName 
--  FROM HierarchyData
--  UNION ALL
--  SELECT Members.Name, Child.MemberName as [MemberName]
--  FROM Members
--  JOIN HierarchyData Child ON Members.MemberName = Child.Name
--)
--SELECT * FROM Members
--ORDER BY [Name], [MemberName]