SQL Hierarchy loop / Recursive query / self join

Question

I have received a list of Orders that have been either approved or not approved.

What I would like to have is an overview table which contains all "approved" Orders and its Sub orders in one and the same column. Nice to have would be a hierarchy/order level next to it that indicates the depth.

Visualization

I'm confused by your expected hierarchy. I understand that ABC is 1.0 in your example, but aren't ABC.4, ABC.5, ABC.6 at the same level? Shouldn't ABC.3, which is a sub order of ABC.4 be a level 2? — basodre, Sep 04 '20 at 16:16
Here's something I did in Oracle.. It'll be pretty similar.. https://stackoverflow.com/questions/49939839/recursive-subquerying-with-sorting/49948032#49948032 - here is sql server version https://stackoverflow.com/questions/1757260/simplest-way-to-do-a-recursive-self-join — JGFMK, Sep 04 '20 at 16:16
@basodre As such you are right and most likely the example was misleading. The intention is really to create a hierarchy of Order to Sub Order in the same column. — Ulantinho, Sep 04 '20 at 18:37

DhruvJoshi · Answer 1 · 2020-09-05T11:56:11.913

you can use a query like below which uses CTE

see live demo here

Create table Orders (OrderNumber nvarchar(max), Status nvarchar(max));
insert into Orders Values
('ABC','Approved'),
('DEF','Not Approved'),
('GHI','Approved'),
('JKL','Approved');


Create table OrderHistory (OrderNumber nvarchar(max), SubOrders nvarchar(max));
insert into OrderHistory Values
('ABC','ABC.4'),
('ABC','ABC.5'),
('ABC','ABC.6'),
('ABC.4','ABC.3'),
('ABC.3','ABC.2'),
('ABC.2','ABC.1');


; with CTE as
(
    select 
        OrderNumber, 
        Number    = 0,
        Level     = ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
    from Orders
        where Status='approved'
    union all
    
    select 
        OrderNumber =   S.SubOrders,
        Number      =   CAST(SUBSTRING(S.SubOrders,CHARINDEX('.',S.SubOrders)+1, LEN(S.SubOrders)-CHARINDEX('.',S.SubOrders)) AS INT),
        Level       =   C.Level
    from CTE C JOIN
        OrderHistory S 
            on S.OrderNumber=C.OrderNumber
)

select 
    OrderNumber, 
    Hierarchy   = CONCAT(Level,'.',Number) 
from  CTE
Order by Level,CASE WHEN Number =0 THEN 99999 ELSE Number END DESC

The SubOrder Table is very large and only the 3-4 levels are in focus.

for this change your CTE to be like

; with CTE as
(
    select 
        OrderNumber, 
        Number    = 0,
        Level     = ROW_NUMBER() OVER (ORDER BY (SELECT NULL)),
        SubLevel  = 1

    from Orders
        where Status='approved'
    union all

    select 
        OrderNumber =   S.SubOrders,
        Number      =   CAST(SUBSTRING(S.SubOrders,CHARINDEX('.',S.SubOrders)+1, LEN(S.SubOrders)-CHARINDEX('.',S.SubOrders)) AS INT),
        Level       =   C.Level,
        SubLevel    =  C.SubLevel +1
    from CTE C JOIN
        OrderHistory S 
            on S.OrderNumber=C.OrderNumber
               and C.SubLevel<5
)

This is nice - and I am almost where I want to be :-) The "Number" logic is based around the "." of the suborders. How do we change it to a regular rank - regardless of the "." ? — Ulantinho, Sep 04 '20 at 19:06
And lastly, can I limit the number of recursive lookup's per suborder? Eg only return max 3 sub order levels of an Order? The SubOrder Table is very large and only the 3-4 levels are in focus. — Ulantinho, Sep 04 '20 at 20:09
Also if I understand what you meant by removing the Number logic- replace last select with `select OrderNumber, Hierarchy = CONCAT(Level,'.',SubLevel) from CTE Order by Level ASC,SubLevel ASC` — DhruvJoshi, Sep 05 '20 at 12:02

SQL Hierarchy loop / Recursive query / self join

1 Answers1