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.
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;
1 AS Lvl,
N'/' + CONVERT(NVARCHAR(4000),crt.CompanyID) + N'/' AS CompanyNode_AsChar
FROM @Company crt
WHERE crt.ParentCompanyID = @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
CONVERT(HIERARCHYID, CompanyNode_AsChar) AS CompanyNode
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