Home » SQL & PL/SQL » SQL & PL/SQL » Convert Relational table into Hierarchical table (Oracle 10g)
Convert Relational table into Hierarchical table [message #397996] Tue, 14 April 2009 14:56 Go to next message
sivakumar121
Messages: 27
Registered: April 2009
Junior Member
Dear All,

Can you please guide me the best approach to convert relational table data into hierarchical table using SQL.
The relational table is a huge table having entire organization supervisor info.

Sample supervisor table data.
SUP1   SUP2   SUP3   SUP4   SUP5
111    221   
111    222    331
111    222    331    441
111    222    331    441    551
111    223    332


I would like to convert this to hierarchical format table using best approach -

SUP_ID  SUP_LEVEL   PARENT_ID
111     1
221     2           111
222     2           111
223     2           111
331     3           222
332     3           223
441     4           331
551     5           441


Your expert Comments needed.
Re: Convert Relational table into Hierarchical table [message #398014 is a reply to message #397996] Tue, 14 April 2009 19:03 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
I reckon a series of UNION statements - one for each level - would be the simplest and fastest way.
SELECT sup1 as sup_id, 1 AS sup_level, null as parent_id
...
UNION ALL
SELECT sup2 as sup_id, 2 AS sup_level, sup1 as parent_id
...
UNION ALL


Ross Leishman
Re: Convert Relational table into Hierarchical table [message #398071 is a reply to message #398014] Wed, 15 April 2009 02:26 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I reckon you need a distinct in there too....
Re: Convert Relational table into Hierarchical table [message #398082 is a reply to message #397996] Wed, 15 April 2009 03:14 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This can also be done with a row generator (of 5 lines).

Regards
Michel
Re: Convert Relational table into Hierarchical table [message #398464 is a reply to message #397996] Thu, 16 April 2009 06:12 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
drop table t purge;
create table t (sup1 integer, sup2 integer, sup3 integer, sup4 integer, sup5 integer);
insert into t values (111,    221,   null,   null,   null);
insert into t values (111,    222,    331,   null,   null);
insert into t values (111,    222,    331,    441,   null);
insert into t values (111,    222,    331,    441,    551);
insert into t values (111,    223,    332,   null,   null);
commit;

SQL> select * from t;
      SUP1       SUP2       SUP3       SUP4       SUP5
---------- ---------- ---------- ---------- ----------
       111        221
       111        222        331
       111        222        331        441
       111        222        331        441        551
       111        223        332

5 rows selected.

SQL> select sup1 sup_id, 1 sup_level, null parent_id from t
  2  union
  3  select sup2, 2, sup1 from t where sup2 is not null
  4  union
  5  select sup3, 3, sup2 from t where sup3 is not null
  6  union
  7  select sup4, 4, sup3 from t where sup4 is not null
  8  union
  9  select sup5, 5, sup4 from t where sup5 is not null
 10  order by 2, 1
 11  /
    SUP_ID  SUP_LEVEL  PARENT_ID
---------- ---------- ----------
       111          1
       221          2        111
       222          2        111
       223          2        111
       331          3        222
       332          3        223
       441          4        331
       551          5        441

8 rows selected.

SQL> with levels as (select level sup_level from dual connect by level <= 5)
  2  select distinct 
  3         decode(sup_level, 1,sup1, 2,sup2, 3,sup3, 4,sup4, 5,sup5) sup_id,
  4         sup_level,
  5         decode(sup_level, 1,to_number(null), 2,sup1, 3,sup2, 4,sup3, 5,sup4) parent_id
  6  from t, levels
  7  where decode(sup_level, 1,sup1, 2,sup2, 3,sup3, 4,sup4, 5,sup5) is not null
  8  order by 2, 1
  9  /
    SUP_ID  SUP_LEVEL  PARENT_ID
---------- ---------- ----------
       111          1
       221          2        111
       222          2        111
       223          2        111
       331          3        222
       332          3        223
       441          4        331
       551          5        441

8 rows selected.

Regards
Michel

[Updated on: Thu, 16 April 2009 06:15]

Report message to a moderator

Re: Convert Relational table into Hierarchical table [message #398813 is a reply to message #398464] Fri, 17 April 2009 13:24 Go to previous message
sivakumar121
Messages: 27
Registered: April 2009
Junior Member
Thanks all, This really helped.
Previous Topic: Procedure error [merged]
Next Topic: UTL_FILE file open modes
Goto Forum:
  


Current Time: Sat Dec 03 13:58:22 CST 2016

Total time taken to generate the page: 0.09449 seconds