SQL join/merge two hierarchyid tables

Question

Is there any way to join/merge two hierarchical tables in SQL Server?

I have two sample tables: BOM and ComponentDetail

declare @BOM table 
    (
      BomNode hierarchyid primary key,
      ComponentID int
    )

insert into @BOM 
values
    ('/',             NULL),
    ('/1/',           1),
    ('/1/1/',         2),
    ('/1/2/',         3),
    ('/1/3/',         4)

declare @ComponentDetail table
   (
       CompNode hierarchyid primary key,
       ComponentID int,
       SteelProductID int
   )

insert into @ComponentDetail 
values
   ('/',          NULL,NULL),
   ('/1/',        2, NULL),
   ('/1/1/',      2,1),
   ('/1/2/',      2,2)

What I want to do is to combine those two tables temporarily just to view the result in my application:

UPDATE: @Sean Lange I've made a mistake in declaring Result table - it should look like the one below:

insert into @Result 
    values 
       ('/',     NULL, NULL),
       ('/1/',   1, NULL),
       ('/1/1/',   2, NULL),
       ('/1/1/1/', NULL, 1),
       ('/1/1/2/', NULL, 2),
       ('/1/2/',   3, NULL),
       ('/1/3/',   4, NULL)

select 
    Node.ToString() as NodeChar, ComponentID, SteelProductID 
from @Result

Here is a diagram of desired output: Output diagram

Anyone ?

It's much easier if you aren't storing individual record ID values as "forward slash delimited" (?) values in a column. You could use a recursive CTE to easily piece together all the levels of the BOM. With this, it looks like you'll have to first parse out your data and normalize it and then use another query to return the data you need. — Jacob H, Dec 20 '17 at 14:09
@JacobH that isn't delimited data, the OP is using the hierarchyid datatype. https://learn.microsoft.com/en-us/sql/t-sql/data-types/hierarchyid-data-type-method-reference — Sean Lange, Dec 20 '17 at 14:52
Ah that's neat. Never used a hierarchyid type. Learn something new every day. — Jacob H, Dec 20 '17 at 14:54

Sean Lange · Answer 1 · 2017-12-20T20:24:21.677

0

You can join those two tables together just like any other tables. In this case a full outer join is probably what you want.

This returns your desired output from your sample data:

select Node = coalesce(b.BomNode.ToString(), c.CompNode.ToString())
    , ComponentID = coalesce(b.ComponentID, c.ComponentID)
    , c.SteelProductID
from @BOM b
full outer join @ComponentDetail c on c.CompNode = b.BomNode

--EDIT--

With my new understanding I think it is something like this (but it isn't quite right on the hierarchy stuff but not clear to me what you want there):

select distinct Node = coalesce(b.BomNode.ToString(), c.CompNode.ToString()) + coalesce(convert(varchar(4), c.ComponentID) + '/', '')
    , ComponentID = coalesce(b.ComponentID, c.ComponentID)
    , c.SteelProductID
from @BOM b
full outer join @ComponentDetail c on b.ComponentID = c.ComponentID

edited Dec 20 '17 at 20:24

answered Dec 20 '17 at 14:58

Sean Lange

33,028
3
25
40

Thank you Sean for the answer, unfortunately it is not what result should look like. With your query I get ambigous output: https://ibb.co/mUz6Pm and it's not correct. – exegee Dec 20 '17 at 17:26
Probably you want to join on ComponentID instead. But I am a bit confused by your desired output. Where does '/4/' come from? It doesn't exist in your sample data. – Sean Lange Dec 20 '17 at 17:33
/4/ comes from BOM table ( node number: /1/3/ ). I want to join them on ComponentID and in this particular sample on ComponentID = 2. BOM table consists of assembly/part dependency but some components may consist of SteelProducts and that information I can get from ComponentDetail. I apologize for my English :) – exegee Dec 20 '17 at 17:45
I've made a mistake in my result table - look up at the question :) – exegee Dec 20 '17 at 19:44
Almost there: https://ibb.co/jVceS6 - SteelProductID=2 should be at 1/1/2/ node not 1/2/2/ – exegee Dec 20 '17 at 20:03
How is steel product 2 become 1/1/2? What is the logic there? – Sean Lange Dec 20 '17 at 20:07
Maybe I was not clear about my question and should have asked about **merging/combining** two hierarchical tables together. SteelProducts table consists of: steel shapes, sections, rods, tubes, angles and the like. Now each component ( or part ) are made of those steel products ( component detail table ) and as you already know parts belongs to assemblies ( bom table ) – exegee Dec 20 '17 at 20:19
Ahh gotcha. Simply changed SteelProductID to ComponentID in the Node column should fix this. See if the latest update works now. – Sean Lange Dec 20 '17 at 20:25
That still isn't exactly what you posted. I am having a hard time understanding exactly what you want but you should be able to tweak the code posted to dial in what you want. – Sean Lange Dec 20 '17 at 20:26
Using your updated query and changing the ComponentDetail table (see updated question ) I was able to partly answer my question and now i get this: https://ibb.co/bLUFKm . – exegee Dec 20 '17 at 22:01
Well I don't understand what you are trying to do because all I am getting is this should be a piece of that by merging. If the data had some detail in in it might help but everything is just a number and there is no clarity about what goes where or why. I have been shooting in the dark on this and I don't know what else I can do. The desired output just doesn't make any sense at all. – Sean Lange Dec 20 '17 at 22:05

SQL join/merge two hierarchyid tables

1 Answers1