Denormalize hierarchy 1

[LarrySteele]

I'm working on a query to denormalize hierarchy.

Given an org chart that looks like this...

[tt]
Margaret
|
+----------+-------------+
| |
Mary Robert
| |
+-------+-------+ Michael
| |
Laura Nancy
|
Ted
[/tt]

And stored in a table that might look like this:

[tt]
EMP MGR
Laura Mary
Mary Margaret
Michael Robert
Nancy Mary
Robert Margaret
Ted Nancy
[/tt]

I'm looking for a result set like this:

[tt]
EMP MGR_LVL_1 MGR_LVL_2 MGR_LVL_3
Laura Margaret Mary
Mary Margaret
Michael Margaret Robert
Nancy Margaret Mary
Robert Margaret
Ted Margaret Mary Nancy
[/tt]

While I'm really storing employee numbers, not names, the structure is the same. Basically, I'm looking to have the full management hierarchy for each employee per row.

I know this isn't rocket science, but I haven't been able to find the solution despite searching for hierarchies here, other forums, and my best friend, Google.

What's the best method for assembling this hierarchy?

Thanks in advance,
Larry

 

[Thargy]

http://www.sc.ehu.es/siwebso/KZCC/Oracle_10g_Documentacion/server.101/b10759/queries003.htm

is probably a good place to start.

Regards

T

 

[LarrySteele]

Hm. SYS_CONNECT_BY_PATH looks interesting.

Thanks for pointing me in the right direction.

Larry

 

[Thargy]

I kind of sort of had that in mind. Let us know how you get on, ok? It's always good to hear how things turn out.

Regards

T

 

[LarrySteele]

T,

Thanks again, you've helped make this MUCH easier that it could have been.

I was able to effectively recreate the /name/name/name syntax from the examples. Of course, my next step would be to parse the single field into any number of multiple fields. Duncan Mein had the next step for that:

http://djmein.blogspot.com/2007/03/flatten-out-heirarchy.html.

Thanks again,
Larry

 

[SantaMufasa]

Larry,

For Tharg's fine help, be sure to thank him by clicking the link (in the lower left corner of Tharg's posting:

Thank thargtheslayer
for this valuable post!

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[LarrySteele]

Thanks. Must've gotten distracted by something shiny this morning.