Replicate Hierarchy node to last level

[PabloMC]

Hi, 

I need to know if i have a table like this:

NodeId	NodeName	ParentId
0	Root
1	A	0
2	AA	1
3	AB	2
4	B	0
5	BA	4
6	BB	5
7	BBA	6
8	C	0
9	CA	8

How can i get this result? I'm looking for an easy way. Now i'm doing a walkaround looking for nulls and taking the previus node.  

NodeName	NodeName1	NodeName2	NodeName3	NodeName4
0Root	0Root	0Root	0Root	0Root
A	0Root	A	A	A
AA	0Root	A	AA	AA
AB	0Root	A	AB	AB
B	0Root	B	B	B
BA	0Root	B	BA	BA
BB	0Root	B	BB	BB
BBA	0Root	B	BB	BBA
C	0Root	C	C	C
CA	0Root	C	CA	CA

I'm using Hierarchy prefix but it doesn't replicate the values to the last level. 

Thanks in advance. 

[Vegar · Accepted solution]

I think your have a good approach.

I world do as normal hierarchy followed by an resident load of the hierarchy table. The alt() function should be a good approach for fetching the right field value.

Load
NodeName ,
NodeName1,
Alt(NodeName1,NodeName2) as NodeName2,
Alt(NodeName1,NodeName2, NodeName3) as NodeName3,
Alt(NodeName1,NodeName2, NodeName3, NodeName4) as NodeName4
Resident
HierarchyTable;