How To Iterate Through An Sql Table Which "parent" And Child" Rows Are In Same Table

In a table there are the columns ID, Title and ParentID. ParentID is used for the ID of the entry in the same table which is considered its parent - thus any entry in which ParentI

Solution 1:

Here you are!

WITH n(ID, Title) AS 
                    (SELECT ID, Title
                    FROM YourTable
                    WHERE ID = (SELECT TOP 1 ID FROM YourTable WHERE ParentID ISNULL)
                    UNIONALLSELECT nplus1.ID, nplus1.Title
                    FROM YourTable as nplus1, n 
                    WHERE n.ID = nplus1.ParentID) 
                    SELECT ID, Title FROM n

Solution 2:

To get hierarchy data from self-referencing table, you can use WITH syntax in sql 2008

WITH n(ID) AS 
   (SELECT ID FROM YourTable 
   UNIONALLSELECT nplus1.ID
   FROM YourTable as nplus1, n 
   WHERE n.ID = nplus1.ParentID) 
   SELECT ID FROM n