Home » SQL & PL/SQL » SQL & PL/SQL » Convert Relational table into Hierarchical table (split from hijacked thread)
Convert Relational table into Hierarchical table (split from hijacked thread) [message #400282] Mon, 27 April 2009 07:33 Go to next message
vinod2912
Messages: 6
Registered: April 2009
Location: INDIA
Junior Member
I need a query that does the exact opposite of this. My table structure is as below. All IDs are entered in the ID column and any ID that is a parent finds entry in the PRNTID column. The level columns indicate ID and PRNTID levels. The flag is 0 for any ID that is only a child. Any ID that is a parent or grandparent has flag value 1.

ID PRNTID IDLEVL PRNTLVL Flag
0    6      1      4      0
1    4      1      2      0
2    3      1      2      0
3    5      2      3      1
4    5      2      3      1
5    6      3      4      1 
6           4             1  



The hierarchy I require is as below


CHILD PARENT GRANDPARENT TOPNODE
0     NULL   NULL        6
1     4      5           6 
2     3      5           6 



I can achieve this with insert and update statements. But I am looking for a SELECT statement solution. Any ideas anybody?
Re: Convert Relational table into Hierarchical table [message #400299 is a reply to message #400282] Mon, 27 April 2009 08:06 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please post a working Test case: create table and insert statements along with the result you want with these data as well as your Oracle version (4 decimals).

Regards
Michel
Re: Convert Relational table into Hierarchical table [message #400388 is a reply to message #400299] Tue, 28 April 2009 00:25 Go to previous messageGo to next message
vinod2912
Messages: 6
Registered: April 2009
Location: INDIA
Junior Member
Hi Michel,

Oracle version is 9.2.0.5.0. Below are the DML for the source table -
create table hier_tab
(id varchar(2), prntid varchar(2), idlevl varchar(2), prntlevl varchar(2), flag varchar(2))

insert into hier_tab values (0,6,1,4,0)
insert into hier_tab values (1,4,1,2,0)
insert into hier_tab values (2,3,1,2,0)
insert into hier_tab values (3,5,2,3,1)
insert into hier_tab values (4,5,2,3,1)
insert into hier_tab values (5,6,3,4,1)
insert into hier_tab values (6,'',4,'',1)

I need my output to be structured in a table as below -
CHILD PARENT GRANDPARENT TOPNODE
0     NULL   NULL        6
1     4      5           6 
2     3      5           6 

0 has null values for parent and grandparent since it's parent is 6 which has a level id of 4 (Topmost)

[Updated on: Tue, 28 April 2009 00:40] by Moderator

Report message to a moderator

Re: Convert Relational table into Hierarchical table [message #400389 is a reply to message #400282] Tue, 28 April 2009 00:28 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
>0 has null values for parent and grandparent since it's parent is 6 which has a level id of 4 (Topmost)
Please translate into understandable English.
Re: Convert Relational table into Hierarchical table [message #400414 is a reply to message #400282] Tue, 28 April 2009 02:03 Go to previous messageGo to next message
vinod2912
Messages: 6
Registered: April 2009
Location: INDIA
Junior Member
I should have put it like this. The child 0 has null values in 2nd and 3rd levels because in the source table, it's parent is 6 which falls in the 4th level (identified by level id for 6)

[Updated on: Tue, 28 April 2009 02:07]

Report message to a moderator

Re: Convert Relational table into Hierarchical table [message #400681 is a reply to message #400414] Wed, 29 April 2009 04:45 Go to previous messageGo to next message
vinod2912
Messages: 6
Registered: April 2009
Location: INDIA
Junior Member
Hi,

I will change the data a bit for better understandability.
My source table is as below -


ID PRNTID IDLEVL PRNTLVL Flag
A    G      1      4      0
B    E      1      2      0
C    D      1      2      0
D    F      2      3      1
E    F      2      3      1
F    G      3      4      1 
G           4             1  




The DML for this as below -

create table hier_tab
(id varchar(2), prntid varchar(2), idlevl varchar(2), prntlevl varchar(2), flag varchar(2))

insert into hier_tab values (A,G,1,4,0)
insert into hier_tab values (B,E,1,2,0)
insert into hier_tab values (C,D,1,2,0)
insert into hier_tab values (D,F,2,3,1)
insert into hier_tab values (E,F,2,3,1)
insert into hier_tab values (F,G,3,4,1)
insert into hier_tab values (G,'',4,'',1)



My required output is -

CHILD PARENT GRANDPARENT TOPNODE

 A     NULL   NULL        G
 B     E      F           G 
 C     D      F           G 




Hope it's more clear now.


[Updated on: Wed, 29 April 2009 04:53]

Report message to a moderator

Re: Convert Relational table into Hierarchical table [message #400861 is a reply to message #400282] Thu, 30 April 2009 01:43 Go to previous messageGo to next message
vinod2912
Messages: 6
Registered: April 2009
Location: INDIA
Junior Member
Since there are no replies, can I assume this is not possible using SQL. Razz
Re: Convert Relational table into Hierarchical table [message #400862 is a reply to message #400861] Thu, 30 April 2009 02:03 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I don't know for other but for myself I don't understand the meaning, relationship and usage of each field and don't want to try to understand it from your post.

Regards
Michel

Re: Convert Relational table into Hierarchical table [message #401225 is a reply to message #400681] Sat, 02 May 2009 20:29 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8625
Registered: November 2002
Location: California, USA
Senior Member
Since you are using 9i, you can't use connect_by_root, but you can extract it from sys_connect_by_path to get the root id (child).

SCOTT@orcl_11g> create table hier_tab
  2    (id	 varchar2(2),
  3  	prntid	 varchar2(2),
  4  	idlevl	 varchar2(2),
  5  	prntlevl varchar2(2),
  6  	flag	 varchar2(2))
  7  /

Table created.

SCOTT@orcl_11g> insert all
  2  into hier_tab values ('A','G',1,4,0)
  3  into hier_tab values ('B','E',1,2,0)
  4  into hier_tab values ('C','D',1,2,0)
  5  into hier_tab values ('D','F',2,3,1)
  6  into hier_tab values ('E','F',2,3,1)
  7  into hier_tab values ('F','G',3,4,1)
  8  into hier_tab values ('G','',4,'',1)
  9  select * from dual
 10  /

7 rows created.

SCOTT@orcl_11g> select * from hier_tab order by id
  2  /

ID PR ID PR FL
-- -- -- -- --
A  G  1  4  0
B  E  1  2  0
C  D  1  2  0
D  F  2  3  1
E  F  2  3  1
F  G  3  4  1
G     4     1

7 rows selected.

SCOTT@orcl_11g> set    null	   NULL
SCOTT@orcl_11g> column child	   format a11
SCOTT@orcl_11g> column parent	   format a11
SCOTT@orcl_11g> column grandparent format a11
SCOTT@orcl_11g> column topnode	   format a11
SCOTT@orcl_11g> select	substr (scbp, 1, instr (scbp, ',') - 1) as child, -- root
  2  	     max (parent)			     as parent,
  3  	     max (grandparent)			     as grandparent,
  4  	     max (prntid) keep (dense_rank last order by prntlevl nulls first) as topnode
  5  from    (select  substr (sys_connect_by_path (id, ','), 2) || ',' as scbp,
  6  		      decode (prntlevl, 2, prntid)		as parent,
  7  		      decode (prntlevl, 3, prntid)		as grandparent,
  8  		      prntid, prntlevl
  9  	      from    hier_tab
 10  	      start   with idlevl = 1
 11  	      connect by prior prntid = id)
 12  group   by substr (scbp, 1, instr (scbp, ',') - 1)
 13  order   by substr (scbp, 1, instr (scbp, ',') - 1)
 14  /

CHILD       PARENT      GRANDPARENT TOPNODE
----------- ----------- ----------- -----------
A           NULL        NULL        G
B           E           F           G
C           D           F           G

SCOTT@orcl_11g>


Re: Convert Relational table into Hierarchical table [message #401324 is a reply to message #401225] Mon, 04 May 2009 01:46 Go to previous message
vinod2912
Messages: 6
Registered: April 2009
Location: INDIA
Junior Member
Thanks Much, Barbara !!
It works like magic Smile
Previous Topic: Fetching the unmatched records from two queries.
Next Topic: Ignore Duplicate Rows
Goto Forum:
  


Current Time: Sun Dec 04 04:35:59 CST 2016

Total time taken to generate the page: 0.05018 seconds