Find the child of a specific until the 5th child in SQL Server

Question

I have a table with this 2 columns workitem_id, parent_workitem_id.

I need to show on a single table the data in this format:

workitem, lvl1_child, lvl2_child, lvl3_child, lvl4_child, lvl5_child

I think I could do it with a recursive query, but I don't know how to write that.

Answer 1

You can use either left join to achive it. Also if you dont want that records does not have child then you may use Inner join to avoid the null values.

Here I have demonstrate example with left join for your case. Use the below query to see the output.

-- create

CREATE TABLE Workitem (
  workitem_id int,
  parentWorkItem_id int
);

insert into Workitem values(1,0);
insert into Workitem values(2,1);
insert into Workitem values(3,1);
insert into Workitem values(4,2);
insert into Workitem values(5,3);
insert into Workitem values(6,2);
insert into Workitem values(7,3);
insert into Workitem values(8,5);
insert into Workitem values(9,4);
insert into Workitem values(10,8);
insert into Workitem values(11,9);


-- fetch 
SELECT w.workitem_id, lv1.workitem_id lv1_child, lv2.workitem_id lv2_child, lv3.workitem_id lv3_child, lv4.workitem_id lv4_child FROM Workitem w
left join Workitem lv1 on lv1.parentWorkItem_id = w.workitem_id
left join Workitem lv2 on lv2.parentWorkItem_id = lv1.workitem_id and lv1.parentWorkItem_id = w.workitem_id
left join Workitem lv3 on lv3.parentWorkItem_id = lv2.workitem_id and lv2.parentWorkItem_id = lv1.workitem_id and lv1.parentWorkItem_id = w.workitem_id
left join Workitem lv4 on lv4.parentWorkItem_id = lv3.workitem_id and lv3.parentWorkItem_id = lv2.workitem_id and lv2.parentWorkItem_id = lv1.workitem_id and lv1.parentWorkItem_id = w.workitem_id
left join Workitem lv5 on lv5.parentWorkItem_id = lv4.workitem_id  and lv4.parentWorkItem_id = lv3.workitem_id and lv3.parentWorkItem_id = lv2.workitem_id and lv2.parentWorkItem_id = lv1.workitem_id and lv1.parentWorkItem_id = w.workitem_id

GO

Please let me know if anything found.