[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 214
  • Last Modified:

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
0
jvoconnell
Asked:
jvoconnell
  • 2
1 Solution
 
sdstuberCommented:
SELECT REGEXP_SUBSTR(ids, '[^/]+', 1, 1) drug_id1,
       REGEXP_SUBSTR(names, '[^/]+', 1, 1) drug_name1,
       REGEXP_SUBSTR(ids, '[^/]+', 1, 2) drug_id2,
       REGEXP_SUBSTR(names, '[^/]+', 1, 2) drug_name2,
       REGEXP_SUBSTR(ids, '[^/]+', 1, 3) drug_id3,
       REGEXP_SUBSTR(names, '[^/]+', 1, 3) drug_name3,
       REGEXP_SUBSTR(ids, '[^/]+', 1, 4) drug_id4,
       REGEXP_SUBSTR(names, '[^/]+', 1, 4) drug_name4,
       REGEXP_SUBSTR(ids, '[^/]+', 1, 5) drug_id5,
       REGEXP_SUBSTR(names, '[^/]+', 1, 5) drug_name5,
       REGEXP_SUBSTR(ids, '[^/]+', 1, 6) drug_id6,
       REGEXP_SUBSTR(names, '[^/]+', 1, 6) drug_name6,
       REGEXP_SUBSTR(ids, '[^/]+', 1, 7) drug_id7,
       REGEXP_SUBSTR(names, '[^/]+', 1, 7) drug_name7,
       REGEXP_SUBSTR(ids, '[^/]+', 1, 8) drug_id8,
       REGEXP_SUBSTR(names, '[^/]+', 1, 8) drug_name8
  FROM (SELECT     SYS_CONNECT_BY_PATH(drug_id, '/') ids,
                   SYS_CONNECT_BY_PATH(drug_name, '/') names,
                   CONNECT_BY_ISLEAF isleaf
              FROM yourtable
        CONNECT BY drug_parent = PRIOR drug_id
        START WITH drug_parent = 0)
 WHERE isleaf = 1
0
 
jvoconnellAuthor Commented:
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.
0
 
sdstuberCommented:
glad I could help
0

Featured Post

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now