CODEWITHSUNDEEP
oracle-databaseplsql
Jay
Jay

Reputation: 23

Selecting child parent hierarchy in a single row pl sql

Oracle 11g - Select query help.

The table "data_table" has three fields as below and keep a child parent relation as follows -

user_code   -> description  -> parent_code

A          -> Root ID   ->    B

B        ->   Level1    ->    C

C      ->     Level2    ->    D

D    ->       Level3    ->    E

E       ->    Level4    ->     F

The requirement is to post this hierarchy in a single line as below.

**
Root ID -> Level1 ->    Level2  -> Level3->     Level4

A   -> B    -> C    -> D    -> E **

What are my options? please help..

Now I use below query, but doesn't looks good. 

 SELECT e.user_code   user_code,
    d.description user_description,
    e.parent_code level1,
    a.parent_code level2 ,
    b.parent_code level3 ,
    c.parent_code level4
FROM    data_table e,
    data_table a,
    data_table b,
    data_table c,
    data_table d
WHERE   e.user_code = d.user_code
AND     b.user_code = a.parent_code
AND     c.user_code = b.parent_code
AND     e.parent_code = a.user_code

Upvotes: 1

Views: 2295

Answers (2)

Boneist
Boneist

Reputation: 23578

If there's a fixed number of levels that you know about, you can do the following:

with sample_data as (select 'A' user_code, 'Root ID' description, 'B' parent_code from dual union all
                     select 'B' user_code, 'Level 1' description, 'C' parent_code from dual union all
                     select 'C' user_code, 'Level 2' description, 'D' parent_code from dual union all
                     select 'D' user_code, 'Level 3' description, 'E' parent_code from dual union all
                     select 'E' user_code, 'Level 4' description, 'F' parent_code from dual union all
                     select 'G' user_code, 'Root ID' description, 'H' parent_code from dual union all
                     select 'H' user_code, 'Level 1' description, 'I' parent_code from dual union all
                     select 'I' user_code, 'Level 2' description, 'J' parent_code from dual union all
                     select 'J' user_code, 'Level 3' description, 'K' parent_code from dual),
             res as (select sd.user_code,
                            sd.description,
                            connect_by_root(sd.user_code) top_node
                     from   sample_data sd
                     connect by sd.user_code = prior sd.parent_code
                     start with sd.description = 'Root ID')
select root_id,
       level1,
       level2,
       level3,
       level4
from   res
pivot  (max(user_code) for description in ('Root ID' as root_id,
                                           'Level 1' as level1,
                                           'Level 2' as level2,
                                           'Level 3' as level3,
                                           'Level 4' as level4))
order by top_node;

ROOT_ID LEVEL1 LEVEL2 LEVEL3 LEVEL4
------- ------ ------ ------ ------
A       B      C      D      E     
G       H      I      J

If there's an unknown number of levels, then you're going to have to investigate dynamic pivoting - there are plenty of examples of how to do dynamic pivoting if you search Google.

Upvotes: 0

pablomatico
pablomatico

Reputation: 2242

You have to use a hierarchichal query connecting parent and child through user_code and parent_code. Something like this:

WITH data_table AS (
   SELECT 'A' user_code, 'Root ID' description, 'B' parent_code FROM dual
   UNION
   SELECT 'B' user_code, 'Level1' description, 'C' parent_code FROM dual
   UNION
   SELECT 'C' user_code, 'Level2' description, 'D' parent_code FROM dual
   UNION
   SELECT 'D' user_code, 'Level3' description, 'E' parent_code FROM dual
   UNION
   SELECT 'E' user_code, 'Level4' description, 'F' parent_code FROM dual
) 
SELECT MAX(SYS_CONNECT_BY_PATH(user_code,'->'))
FROM data_table
CONNECT BY prior parent_code = user_code 
START WITH user_code = 'A'

SYS_CONNECT_BY_PATH shows the path but if you want the whole path in a single line you need to get the MAX.

You can read more on hierarchichal queries here

Upvotes: 1

Related Questions