How to get the full Hierarchy with SQL CTE

Question

Hi I am trying to get the full hierarchy of my category. Here is my sample table

ID     PARENT_ID       NAME          DEPTH
------------------------------------------
1      NULL            A             1
2      NULL            B             1
3      NULL            C             1
4      1               D             2
5      4               E             3

The cte output should be this

ID     PARENT_ID       NAME
---------------------------
1      NULL            A             
2      NULL            B             
3      NULL            C             
4      1               D             
5      4               E             
5      1               E  

As you can see id:5 is parent of 4 and 1. How do i query the whole tree

Answer 1

In SQL Server 2008 CTE can be use to query recursively.

Example of CTE from MSDN

-- Sample Solution (Untested)

;With TableCTE(Id, Name, ParentId, Depth)
(
   Select ID,Name,ParentId, 1
     FROM MyTable

   Union All

   Select C.Id, C.Name, t.ParentId, c.Depth +1
     FROM @tmp t
    INNER JOIN TableCTE C on t.Id = c.ParentId
    -- Where t.ParentId IS Not Null
)

SELECT Id, Name, ParentId
FROM TableCTE

Answer 2

DECLARE @tmp TABLE(ID INT,ParentID INT,NAME VARCHAR(10),DEPTH INT)

INSERT INTO @tmp VALUES
(1      ,NULL            ,'A'             ,1 ),
(2      ,NULL            ,'B'             ,1 ),
(3      ,NULL            ,'C'             ,1 ),
(4      ,1               ,'D'             ,2 ),
(5      ,4               ,'E'            ,3 ),
(6      ,5               ,'F'            ,4 );

select * from @tmp

;WITH cte AS
(
    SELECT   A.ID
            ,A.ParentID
            ,A.NAME
            ,A.DEPTH
    FROM    @tmp A

    UNION ALL

    SELECT   A.ID
            ,B.ParentID
            ,A.NAME
            ,A.DEPTH 
    FROM    cte A 
    INNER JOIN @tmp B on A.ParentID = B.ID 
    WHERE   B.ParentID IS NOT NULL

)

SELECT * FROM cte

Answer 3

;WITH cte AS
(
    SELECT   A.ID
            ,A.ParentID
            ,A.NAME
            ,A.DEPTH
    FROM    @tmp A
    WHERE   A.ParentID IS NULL

    UNION ALL

    SELECT   B.ID
            ,B.ParentID
            ,B.NAME
            ,B.DEPTH 
    FROM    cte A 
              INNER JOIN 
            @tmp B on B.ParentID = A.ID
    WHERE   B.ParentID IS NOT NULL

)

SELECT * FROM cte