Categories
Bookmarked Database MS SQL Server SQL

Advance SQL: Finding Hierarchical data using Recursive CTE

Often we have a Table that store Hierarchical data, such as any shopping cart will have product category in table that store parent table within same table. We often use such information. The typical structure of table is

ID, Name , ParentID

Where ParentID is ID within same table or for Top level it is either Null or Zero. In such tables we often want to find Child of Child in order we can list them in tree view, i.e.  

L0
  L1
    L2
  L1-1
    L2-1
 ....

Now, to find this type of result you have two option: 1. Write complete logic in your code 2. Make SQL do it for you. For those who prefer this method here is the sample Query I used

 

DECLARE @ParentCompanyID INT = 9;
WITH RecComp
AS
(
    SELECT  crt.CompanyID,
            crt.Name,
            crt.ParentCompanyID,
            1 AS Lvl,
            N'/' + CONVERT(NVARCHAR(4000),crt.CompanyID) + N'/' AS CompanyNode_AsChar
    FROM    @Company crt
    WHERE   crt.ParentCompanyID = @ParentCompanyID
    UNION ALL
    SELECT  cld.CompanyID,
            cld.Name,
            cld.ParentCompanyID,
            prt.Lvl + 1,
            prt.CompanyNode_AsChar + CONVERT(NVARCHAR(4000), cld.CompanyID) + N'/'
    FROM    RecComp prt -- parent
    INNER JOIN @Company cld ON prt.CompanyID = cld.ParentCompanyID
)
SELECT  *,
        CONVERT(HIERARCHYID, CompanyNode_AsChar) AS CompanyNode
FROM    RecComp
ORDER BY CompanyNode;

This query make use of CTE feature of SQL server [I personnally test it on SQL server 2008 R2, 2012 and SQL Azure] and HierarchyID to show the result as desire. More can be read in my thread on StackOverFlow here

 

 

By Sumit Gupta

Sumit Gupta, born on 7th Feb 1981 in NCR, India. He lives a quiet life until he turn to a developer. Once he is in his professional life people start recognizing him for his work. Sumit Gupta is always keen to learn more and share his knowledge to anyone who deserve it. His goal of life is to code at least one 3D action game by itself or with his company Vikasumit [http://www.vikasumit.com].