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  |
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 #400388 is a reply to message #400299] |
Tue, 28 April 2009 00:25  |
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 #400681 is a reply to message #400414] |
Wed, 29 April 2009 04:45 |
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 #401225 is a reply to message #400681] |
Sat, 02 May 2009 20:29 |
 |
Barbara Boehmer
Messages: 9104
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>
|
|
|
|
|
|
Goto Forum:
Current Time: Sat Feb 15 01:02:46 CST 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
