Selecting all parents in the order of relation from hierarchical table SQL

Question

I've a table like this with a parent child relation in the same table

AccountID|     ParentID     |   AccountName
----------------------------------------------
1       |   0       |   Root
2       |   1       |   Child1
3       |   1       |   Child2
4       |   2       |   Child3
5       |   4       |   Child1
6       |   5       |   Child1
7       |   6       |   Child1
8       |   6       |   Child1

So when I send the account ID 7 I have to get the tables in the order like child,father,grandfather.. that way.. So for 7, I need to get all parets like this

AccountID
---------
    7
    6
    5
    4
    2
    1

So the most important point is the order. It should be from the bottom level to its next higher then to the next...

Answer 1

You can use a recursive CTE:

declare @childAccID int
set @childAccID = 7  

;WITH Rec_CTE 
    AS(
        SELECT 1 AS Level, 
               tChild.*
        FROM dbo.TableName tChild
        WHERE tChild.AccountID = @childAccID

        UNION ALL

        SELECT Level + 1 AS Level, 
               parent.*
        FROM Rec_CTE tParent
        INNER JOIN  dbo.TableName parent 
          ON parent.AccountID = tParent.ParentID
    )
SELECT * FROM Rec_CTE
ORDER BY Level

DEMO