CODEWITHSUNDEEP
sqlsql-serverhierarchyhierarchical-datahierarchical
Verd'O
Verd'O

Reputation: 101

Build hierarchy table from main nodes and level nodes

I'd like to build a child-parent link from data that look like this:

id_node main_node level
A9 A9 0
92 A9 1
923 A9 2
9234 A9 3

Is there a simple way to reverse this table and add for each row, the parent id and get something like this:

id_node main_node level parent
A9 A9 0 NULL
92 A9 1 A9
923 A9 2 92
9234 A9 3 923

I'm not trying to loop through every id, I'd like to know if there's a method like this one , but kind of reverted.

Upvotes: 0

Views: 69

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271003

You can use join:

select t.*, tparent.id_node as parent
from t left join
     t tparent
     on tparent.main_node = t.main_node and
        tparent.level = t.level - 1;

You can also use lag():

select t.*,
       lag(id_node) over (partition by main_node order by level) as parent
from t

Upvotes: 1

Related Questions