How To Get The Full Hierarchy With Sql Cte

Hi I am trying to get the full hierarchy of my category. Here is my sample table ID PARENT_ID NAME DEPTH ------------------------------------------ 1 NULL

Solution 1:

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

INSERTINTO@tmpVALUES
(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

    UNIONALLSELECT   A.ID
            ,B.ParentID
            ,A.NAME
            ,A.DEPTH 
    FROM    cte A 
    INNERJOIN@tmp B on A.ParentID = B.ID 
    WHERE   B.ParentID ISNOTNULL

)

SELECT*FROM cte

Solution 2:

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, 1FROM MyTable

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

SELECT Id, Name, ParentId
FROM TableCTE

Solution 3:

;WITHcteAS
(
    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 * FROMcte