Parent,Child,SubChild CTE

Question

This is the CTE that I created to get parent account, then child account, then subchild acccount, but it's showing the 4 parents on top.

with AccountCTE(Id,Code,AccountName,[Level])
as
(
  select D.Id, D.Code, D.AccountName, 1
  from DefaultAccount D
  join AccountClass on D.AccountClassId = AccountClass.Id
  where D.ParentAccountId Is Null
  union All
  select Da.Id, Da.Code, Da.AccountName, AccountCTE.Level +1
  from DefaultAccount Da
  join AccountCTE on Da.ParentAccountId = AccountCTE.Id
)
select * from AccountCTE

Answer 1

Try this as your solution...

Here tab is table name Id is your rowId and parentid is your parentId.

  ;WITH CTE AS (
      SELECT Id, ParentId,0 AS [Level], CAST(Id AS varchar(1000)) AS Heirarchy,Id AS TopParentId
      FROM dbo.tab
      WHERE Id IN (SELECT Id FROM tab WHERE ParentId IS NULL)
      UNION ALL

      SELECT mgr.Id, mgr.ParentId, CTE.[Level] +1 AS [Level], 
            CAST(( CAST(mgr.Id AS VARCHAR(1000)) + '>' + CTE.Heirarchy) AS varchar(1000)) AS Heirarchy, CTE.TopParentId
      FROM CTE
        INNER JOIN dbo.tab AS mgr
          ON CTE.Id = mgr.ParentId
    )
    select * from cte order by heirarchy