Link to home
Start Free TrialLog in
Avatar of jvoconnell
jvoconnell

asked on

hierarchical records into rows

Experts,

I have a table with a hierarchy of pharmacy info. It is received on a completely different format than we need.
It is not a fixed amount of levels in the hierarchy. It can be up to 8, but some may only have 2.
The "Drug_Parent" in the drug level one step higher. THe "Ulimate_Parent" is the highest level in that particular hierarchy.
Basically, if the drug has an "Ulitmate Parent" = 0, then that is the top level in the hierarchy.

We've got something to get us started, but we figure that there has to be a better way. Especially if they keep expanding the amount of tiers in the hierarchy.



---raw format
DRUG_ID  DRUG_NAME, DRUG_PARENT,  ULITMATE_PARENT, DRUG_LEVEL
001       PAINKILLER      0            0            1
002           ASPRIN            001            001            2
003      CHILD ASPRIN      002            001            3
007          Anti-Fungal      0            0            1
009         Fungal-Cream      007            007            2      


---Desired Output

DRUG_ID1  DRUG_NAME1  DRUG_ID2  DRUG_NAME2, DRUG_ID3, DRUG_NAME3
001        PAINKILLER  002       ASPRIN                                 003       CHILD ASPRIN
007            anit-Fungal    009       Fungal-Cream                 NULL     NULL
ASKER CERTIFIED SOLUTION
Avatar of Sean Stuber
Sean Stuber

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of jvoconnell
jvoconnell

ASKER

AMAZING! I've never been exposed to that before. We were handling this in 100 lines of code that was extremely confusing to read and took longer to run. This works great. I just had to change the '/' to another value due to an ora-30004 error. Thank you so much.
glad I could help