Reverse hierarchy: how to convert a horizontal hierarchy to an adjacency list?

[did]

Hi everyone,

I was wondering how to reverse a hierarchy?

with other words: how to convert a horizontal hierarchy to an adjacency list model (NodeID, ParentID, Name)?

Many thanks for your ideas

[MarcoWedel]

please provide an example having consistent values between input and output tables to clarify your requirement.

thanks

[did]

Hi Marco, you're right - I didn't see, that the the second table was wrong. I've just put the right one.

[MarcoWedel · Accepted solution]

Doesn't create the NodeIDs quite in the same order, but the relations should be correct:

table1:
CrossTable (ColNam,Name)
Load RecNo() as ID, *
Inline [
Field1, Field2, Field3
A,BB,DDD
A,CC,EEE
A,CC,FFF
A,BB,DDD
];

table2:
Load AutoNumber(Name) as NodeID,
     If(ID=Previous(ID),AutoNumber(Previous(Name))) as ParentID,
     Name
Resident table1;

Drop Table table1;

hope this helps

Marco

[did]

It works!! Many thanks, Marco!