How to make loop that looks into a row if exists pull data if not stop loop from a two rows

[eliasskate_8]

here my table structure

I am looking for all ComponentItemCode for BillNo “GK1935-WPS”. Then I need to look up the result back into BillNo and keep repeating that loop until the result does not exists in to BillNo.

and layout the result in hierarchy table like this

and create a stored procedure so if i type different BillNo will return the hierarchy table for that specific BillNo

[r937]

1, does it ~have~ to be a loop?

2, max 3 levels?

[eliasskate_8]

yes because not all BillNo has 3 level some has more that 3 other less than 3

[r937]

so okay, how many levels do you think there will be?

and ~why~ does it ~have~ to be a loop?

[eliasskate_8]

i think maximum is 10 and i don’t have to be a loop i just right now i do not have any idea how to make this.

[r937]

well here’s the general approach

i’ve coded it for 3 levels, you can easily extend this to 10 if you wish by adding more self-joins

CREATE TABLE eliasskate_8
( BillNo             VARCHAR(39) 
, ComponentItemCode  VARCHAR(39)
, QuantityPerBill    DECIMAL(5,2)  
);
INSERT INTO eliasskate_8
VALUES
 ( 'GK1935-WPS' , 'GK1935-LLD-2-B' , 2.0 )
,( 'GK1935-WPS' , 'GK1935-RDD-2-A' , 2.0 )
,( 'GK1935-WPS' , 'WS-EXPAN' , 1.0 )
,( 'GK1935-WPS' , 'WRCBHSHO-WL' , 1.0 )
,( 'GK1935-WPS' , 'AC-12LD' , 1.0 )
,( 'GK1935-WPS' , 'AC-12UD' , 1.0 )
,( 'GK1935-LLD-2-B' , '02-060-00247' , 7.50 )
,( 'GK1935-LLD-2-B' , '02-020-00028' , 2.0 )
,( 'GK1935-RRD-2-A' , '02-060-00247' , 7.50 )
,( 'GK1935-RRS-2-A' , '02-020-00028' , 2.0 )
,( 'WS-EXPAN' , '02-040-00111' , 8.0 )
,( 'WS-EXPAN' , '02-040-00112' , 8.0 )
,( 'WRCBHSHO-WL' , '02-060-00202' , .8 ) 

SELECT LVL1.BillNo             as LVL1
     , LVL2.BillNo             as LVL2
     , LVL3.BillNo             as LVL3
  FROM eliasskate_8 LVL1
LEFT Outer
  JOIN eliasskate_8 LVL2
    ON LVL2.BillNo = LVL1.ComponentItemCode      
LEFT Outer
  JOIN eliasskate_8 LVL3
    ON LVL3.BillNo = LVL2.ComponentItemCode   
 WHERE LVL1.BillNo = 'GK1935-WPS'
ORDER 
    BY LVL1.BillNo   
     , LVL2.BillNo   
     , LVL3.BillNo  
     
LVL1        LVL2            LVL3
----------  --------------  -----
GK1935-WPS  null            null
GK1935-WPS  null            null
GK1935-WPS  null            null
GK1935-WPS  GK1935-LLD-2-B  null
GK1935-WPS  GK1935-LLD-2-B  null
GK1935-WPS  WRCBHSHO-WL     null
GK1935-WPS  WS-EXPAN        null
GK1935-WPS  WS-EXPAN        null
     

[eliasskate_8]

thank you i appreciate your help

I had tried that before and work but take forever to pull the data with inner join work really fast but i have to know the exact levels for the bill number that why I was trying to loop it and stop de loop when bill number is null.