Recursive query from the same table

Question

I have different product serial numbers in one table ProdHistory which contains, as the table name suggest, production history.
For example I have product serial SER001 which uses parts with its own serial number.
We also produce these parts thus uses the same table ProdHistory to track its subparts.
The same goes with the subparts and if it has sub-sub parts.

Sample Table

IF OBJECT_ID('tempDB.dbo.#SAMPLETable') IS NOT NULL DROP TABLE #SAMPLETable
CREATE TABLE #SAMPLETable
(
    ITEMSEQ INT IDENTITY(1,1),
    SERIAL NVARCHAR(10) COLLATE SQL_Latin1_General_CP850_CI_AS,
    ITEMID NVARCHAR(10) COLLATE SQL_Latin1_General_CP850_CI_AS,
    PARTSERIAL NVARCHAR(10) COLLATE SQL_Latin1_General_CP850_CI_AS,
    PARTID NVARCHAR(10) COLLATE SQL_Latin1_General_CP850_CI_AS,
    CREATEDDATETIME DATETIME
)

INSERT INTO 
       #SAMPLETable (SERIAL,ITEMID,PARTSERIAL,PARTID,CREATEDDATETIME) 
VALUES ('SER0001','ASY-1342','ITM0001','PRT-0808','2017-01-17'),
       ('SER0001','ASY-1342','ITM0002','PRT-0809','2017-01-17'),
       ('SER0001','ASY-1342','ITM0003','PRT-0810','2017-01-17'),
       ('SER0001','ASY-1342','ITM0004','PRT-0811','2017-01-17'),
       ('ITM0001','PRT-0808','UNT0001','PRT-2020','2017-01-16'),
       ('ITM0002','PRT-0809','UNT0002','PRT-2021','2017-01-16'),
       ('ITM0002','PRT-0809','UNT0003','PRT-2022','2017-01-16'),
       ('ITM0003','PRT-0810','UNT0004','PRT-2023','2017-01-16'),
       ('UNT0002','PRT-2021','DTA0000','PRT-1919','2017-01-15'),
       ('UNT0003','PRT-2022','DTA0001','PRT-1818','2017-01-15'),
       ('DTA0001','PRT-1818','LST0001','PRT-1717','2017-01-14')

The question is, if I'm given just the main serial number, how can I return all the parts and subparts serial associated with it?

Sample Result:

MainSerial SubSerial1 SubSerial2 SubSerial3 SubSerial4
-------------------------------------------------------
SER0001    ITM0001    UNT0001
SER0001    ITM0002    UNT0002    DTA0000
SER0001    ITM0002    UNT0003    DTA0001    LST0001
SER0001    ITM0003    UNT0004
SER0001    ITM0004   

In above, it is not definite how many parts and subparts there are for a serial number.
I did not post my code since what I'm doing right now is to query it one by one.
If I have known number of subparts, I can do nested Joins, however it is not.

Another question is, if I'm just given any of the subparts above, is it possible to return the same result?

Answer 1

I think a way is to use Dynamic SQL like this:

-- Variables to generate SQL query string dynamically
declare @cols nvarchar(max) = '', @joins nvarchar(max) = '', @sql nvarchar(max) = '';    

-- Using CTE to iterate parent-child records
with cte(i, cols, joins, itemId, serial, partId, partSerial) as (
    select 
        1, -- Level or depth of hierarchically tree 
        N's1.serial MainSerial, s1.partSerial SubSerial'+cast(1 as varchar(max)), 
        N'yourTable s'+cast(1 as varchar(max)), 
        s.itemId, s.serial, s.partId, s.partSerial
    from yourTable s
    -- A way to filter root-parents is filtering items those are not in parts
    where s.itemId not in (select si.partId from yourTable si)
    union all
    select 
        i+1, 
        cols + N', s'+cast(i+1 as varchar(max))+N'.partSerial SubSerial'+cast(i+1 as varchar(max)), 
        joins + N' left join yourTable s'+cast(i+1 as varchar(max))+N' on s'+cast(i as varchar(max))+N'.partId = s'+cast(i+1 as varchar(max))+N'.itemId', 
        st.itemId, st.serial, st.partId, st.partSerial
    from cte 
    join #sampleTable st on cte.partId = st.itemId
)
-- Now we need only strings of deepest level
select top(1) 
    @cols = cols, @joins = joins
from cte
order by i desc;

-- Finalize and executing query string
set @sql = N'select ' + @cols + N' from ' + @joins + N' where s1.itemId not in (select s.partId from yourTable s)';
exec(@sql);

Additional Note: Generated query is:

select s1.serial MainSerial
    , s1.partSerial SubSerial1
    , s2.partSerial SubSerial2
    , s3.partSerial SubSerial3
    , s4.partSerial SubSerial4 
  --, ...
from yourTable s1 
  left join yourTable s2 on s1.partId = s2.itemId 
  left join yourTable s3 on s2.partId = s3.itemId 
  left join yourTable s4 on s3.partId = s4.itemId 
--left join ...
where s1.itemId not in (select s.partId from yourTable s);