Return BOM results in "indented" order
I don't really need the results indented that's just the best title I
could come up with. Any help would be most appreciated. I have spent hours
attempting do this via CTE and that appears to be the way to go but I am
stuck.
My table contains the following:
Root_Job Parent_Job Component_Job
1 1 1a
1 1 1b
1 1 1c
1 1a 1a1
1 1a 1a2
1 1b 1b1
1 1b 1b2
2 2 2a
2 2 2b
I am trying to create a view that returns the following:
Root_Job Parent_Job Component_Job
1 1 1a
1 1a 1a1
1 1a 1a2
1 1 1b
1 1b 1b1
1 1b 1b2
1 1 1c
2 2 2a
2 2 2b
Just To clarify the return order I am trying to achieve is:
1
1a
1a1
1a2
1b
1b1
1b2
1c
2
2a
2b
Lastly the CTE I have been attempting but is doing nothing for me is:
with BOM (Root_job, parent_job, component_Job)
as
(
-- Anchor member definition
SELECT e.Root_Job, e.Parent_Job, e.Component_Job
FROM Bill_Of_Jobs AS e
WHERE Root_Job = Parent_Job
UNION ALL
-- Recursive member definition
SELECT e.Root_Job, e.Parent_Job, e.Component_Job
FROM Bill_Of_Jobs AS e
INNER JOIN bill_of_Jobs AS d
ON e.parent_Job = d.Component_Job
)
-- Statement that executes the CTE
SELECT * from BOM
No comments:
Post a Comment