CODEWITHSUNDEEP
sqlsql-serverrecursive-query
Ulantinho
Ulantinho

Reputation: 11

SQL Hierarchy loop / Recursive query / self join

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

Upvotes: 1

Views: 300

Answers (1)

DhruvJoshi
DhruvJoshi

Reputation: 17146

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
)

Upvotes: 1

Related Questions