I need to query some information from a maufacturing system. With the many posts here in this forum I was able to build a recursive query but now I'm stuck with finishing it.

I have a table that shows an assembly that needs to be manufactured and all sub-assemblies and components.

Simplified it looks like this:

Now I'm interested in machine_a and all its components / sub assemblies. This machine consist of one time the front_cover and two times the side_cover and some screws.

To list all components for a specific machine I'm running a recursive query:

declare @assembly nchar(20) = 'machine_a';

WITH whole_machine AS (
    SELECT       
        assembly_parent, 
        component,
        amount,
        0 as Level,
        amount as total
   FROM       
        manufacturing
    WHERE assembly_parent=@assembly
    UNION ALL
    SELECT 
        man.assembly_parent, 
        man.component,
        man.amount,
        Level   1,
        man.amount * mach.total as total
    FROM 
        manufacturing man
        INNER JOIN whole_machine mach
        ON mach.component = man.assembly_parent
)

select * from whole_machine;

That works as expected and I'm getting "machine_a" with all its components and the correct level:

Is there a way to have that list sorted by hierarchy? So instead of having listed anything with Level 0 first, then Level 1, then Level 2, etc. to rather show it in the order the components belong together? Kind of like this:

machine_a
    front_cover
        screw
        sheet_metal_a
    side_cover
        screw
        sheet_metal_b

That means it's the list above but it should show in this order:

CodePudding user response：

You're very close. You need a lineage for all of the parts. If each of the rows in your table has an ID, you can construct such a lineage with the CTE that you're already using.

declare @assembly nchar(20) = 'machine_a';

with manufacturing as (
    select * from (values
        (1, 'machine_a', 'front_cover', 1),
        (2, 'machine_a', 'side_cover', 2),
        (3, 'front_cover', 'screw', 4),
        (4, 'front_cover', 'sheet_metal_a', 1),
        (5, 'side_cover', 'screw', 4),
        (6, 'side_cover', 'sheet_metal_b', 1),
        (7, 'machine_b', 'box', 1),
        (8, 'box', 'screw', 8),
        (9, 'box', 'sheet_metal', 1)
    ) as x(id, assembly_parent, component, amount)
)
, whole_machine AS (
    SELECT       
        assembly_parent, 
        component,
        amount,
        amount as total,

        0 as Level,
        h = cast( concat('/', id, '/') as varchar(max))
   FROM       
        manufacturing
    WHERE assembly_parent=@assembly
    UNION ALL
    SELECT 
        man.assembly_parent, 
        man.component,
        man.amount,
        man.amount * mach.total,

        Level   1,
        h = cast( concat(mach.h, man.id, '/' ) as varchar(max))
    FROM 
        manufacturing man
        INNER JOIN whole_machine mach
        ON mach.component = man.assembly_parent
)

select *
from whole_machine
order by h;

Note, h can be converted to a hierarchyid (which I'm a big fan of). It has the advantage of being able to be persisted into a table alongside your data so instead of needing to construct the lineage at query time, it can be done at data mutation time. I can write that up if that's something you're interested in exploring.