How to show Parent Child Hierarchy without CTE

Question

Here i use CTE to show parent child Hierarchy but curious to know without CTE how can i display parent child hierarchy?

;WITH DirectReports as 
(
    SELECT      CSM_ID, 
                ID, 
                ParentID, 
                DisplayInCSM, 
                Type,
                FontName,
                FontStyle,
                FontSize,
                UnderLine,
                BGColor,
                LineItemID,
                Presentation,
                BrokerOrientation,
                AnnualFormat,
                CalculationMethod,
                Indent,
                FGColor,
                Box,
                HeadingSubHeading,
                ColOrder,
                @TickerID AS TickerID,
                hierarchy = FORMAT(ID,'0000'), 
                level = 0

    FROM        tblCSM_ModelDetails 
    WHERE       ISNULL(ParentID, 0) = 0
                AND Type<>'BM'
                AND CSM_ID=@CSM_ID

    UNION ALL
    SELECT      e.CSM_ID, 
                e.ID, 
                e.ParentID, 
                e.DisplayInCSM, 
                e.Type,
                e.FontName,
                e.FontStyle,
                e.FontSize,
                e.UnderLine,
                e.BGColor,
                e.LineItemID,
                e.Presentation,
                e.BrokerOrientation,
                e.AnnualFormat,
                e.CalculationMethod,
                e.Indent,
                e.FGColor,
                e.Box,
                e.HeadingSubHeading,
                e.ColOrder,
                @TickerID AS TickerID,
                hierarchy = d.hierarchy + '.' + FORMAT(e.id,'0000'), 
                level = level + 1

    FROM        tblCSM_ModelDetails e
    JOIN        DirectReports d on e.ParentID = d.ID
    WHERE       e.Type<>'BM'
                AND e.CSM_ID=@CSM_ID
)

Answer 1

It's painful without a CTE, which is sort of designed to solve this problem, but it is doable. Performance can become an issue though. The book SQL Hacks from O'Reilly has an example IIRC. But it highly depends upon your table structure and how many levels you need to support. For a truly generic solution I think you have to resort to a temporary table and looping but I could be remembering wrong. This SO post shows one way of doing it but, again, it depends upon your table structure.

Answer 2

Hi @T.Zacks ,

Recursive cte is a simple way to display hierarchical data, so far I have not tried other methods. Perhaps it can be implemented with dynamic SQL and loops, but that is complicated and meaningless.

If you have any question, please feel free to let me know.

Regards
Echo

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 3

hey @T.Zacks ,
Ideally Recursive CTE is the best way to dynamically get parent child relations.
There are other ways as well:

1. using while loop
2. In case if you know till what level the max relationship can go, you can us multiple join statements with same table

Answer 4

If you make two functions — GetHierarchy and GetLevel — then you can write something like this (instead of CTE):

select *, dbo.GetHierarchy(CSM_ID) as hierarchy, dbo.GetLevel(CSM_ID) as level
from tblCSM_ModelDetails
order by hierarchy

These functions do not seem complicated and will use loops. (Maybe you can also make a single function and use CROSS APPLY). The performance will be probably not better than CTE.

Answer 5

The required helper functions are:

create function GetLevel(@id int)  
returns int  
as  
begin  
    declare @level int = 0  
  
    while 1=1  
    begin  
        select @id = ParentID from tblCSM_ModelDetails where ID = @id  
        if @@ROWCOUNT = 0 break  
        set @level += 1  
    end  
  
    return @level  
end  
go  
  
create function GetHierarchy(@id int)  
returns varchar(max)  
as  
begin  
    declare @hierarchy varchar(max) = format(@id, '0000')  
  
    while 1=1  
    begin  
        select @id = ParentID from tblCSM_ModelDetails where ID = @id  
        if @@ROWCOUNT = 0 or @id is null break  
        set @hierarchy = FORMAT(@id, '0000') + '.' + @hierarchy  
    end  
  
    return @hierarchy  
end  
go  

Pass the primary key, which is probably ID:

select *, dbo.GetLevel(ID) as level, dbo.GetHierarchy(ID) as hierarchy  
from tblCSM_ModelDetails  
order by hierarchy  

Maybe you can also pass @Craig _ID for additional filtering, but the purpose of this column is not clear.