Create query on single table that create sub levels

Question

I have been task to create a query on a table that actually has multiple level in it.

The way the table is organize is as follows. I have the following columns

SYSID, NODE_TYPE, PARENT, ISFIRST, NEXT, DOCNO

• SYSID is a unique Identifier for each row.
• NODE_TYPE is a value either F or D. F is for folder and D is for documents
• PARENT is the parent folder where documents are supposed to be stored.
• ISFIRST tell if this is the first value in the folder or not.
• NEXT is the SYSID of the next value in the folder.

I must write a query that will find the folder, find all the entries in the folder, find if a sub folder exists and all sub values in the sub folders that could exists bellow that folder.

Something as shown in the screenshot.

The process can be many levels down.

Does anyone would have any idea on how I can create this?

I tried to create a cursor but I was not successful as it only went down one extra level.

I was thinking about creating a while loop but I do not have anything that I can think on using to perform the look without causing an infinite loop.

Any help would be appreciated.

Answer 1

This query does not answer the question in full because your question doesn't completely explain the desired results

You can use window function and Cte to solve the problem:

;WITH MyTest as
(
  SELECT P.SYSID, P.PARENT,p.NODE_TYPE,p.ISFIRST,p.DOCNO, CAST(P.SYSID AS VarChar(Max)) as Level
  FROM a P
  WHERE P.ISFIRST = 1

  UNION ALL

  SELECT P1.SYSID, P1.PARENT,P1.NODE_TYPE,P1.ISFIRST,P1.DOCNO, CAST(P1.SYSID AS VarChar(Max)) + ', ' + M.Level
  FROM a P1  
  INNER JOIN MyTest M
  ON M.SYSID = P1.PARENT
 )
SELECT * From MyTest
order by SYSID

You can insert the basic data with the following statements:

create table a(
SYSID int,
NODE_TYPE varchar(100), 
PARENT int, 
ISFIRST bit, 
NEXT int, 
DOCNO int
)
insert into a
(            SYSID,NODE_TYPE,PARENT,ISFIRST,NEXT,DOCNO)
      select 1 as SYSID,'f' as NODE_TYPE     ,null as PARENT,1 as ISFIRST,2     as NEXT,null as DOCNO
union select 2 as SYSID,'f' as NODE_TYPE     ,1    as PARENT,0 as ISFIRST,3     as NEXT,null as DOCNO
union select 3 as SYSID,'D' as NODE_TYPE     ,2    as PARENT,0 as ISFIRST,3     as NEXT,null as DOCNO
union select 4 as SYSID,'D' as NODE_TYPE     ,1    as PARENT,0 as ISFIRST,5     as NEXT,null as DOCNO

union select 5 as SYSID,'f' as NODE_TYPE     ,null as PARENT,1 as ISFIRST,6     as NEXT,null as DOCNO
union select 6 as SYSID,'D' as NODE_TYPE     ,5    as PARENT,0 as ISFIRST,null     as NEXT,null as DOCNO