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