Hierarchy / Parents

[#1]

Hi all,

i have a problem with a table that i LOAD and i want to restructure. The source table looks somewhat like follws:

LocationID	LocationName	ParentLocation	Depth
1	World		0
2	Europe	1	1
3	America	1	1
4	Africa	1	1
5	South America	3	2
6	Eastern Europe	2	2
7	Sao Paolo	5	3
8	Moscau	6	3

What i want is:

LocationID	Location	SubLocationID	SubLocation	SubSubLocationID	SubSubLocation	SubSubSubLocationID	SubSubSubLocation
1	World	2	Europe	6	Eastern Europe	8	Moscau
1	World	3	America	5	South America	7	Sao Paolo
1	World	4	Africa	-	-	-	-

Can someone help ?

Thanks a lot!

[Gysbert_Wassenaar]

Here's a simple example using the hierarchy function to create a hierarchy table.

[#3]

Thanks, but i need result table like the one i sketched...

[#4]

Hi Phillipe,

You can try using a cross table,you can achieve the format :

LocationID	Location	SubLocationID	SubLocation	SubSubLocationID	SubSubLocation	SubSubSubLocationID	SubSubSubLocation
1	World	2	Europe	6	Eastern Europe	8	Moscau
1	World	3	America	5	South America	7	Sao Paolo
1	World	4	Africa	-	-	-	-

Hope this helps.

Rochelle