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