OraFAQ Forum: SQL & PL/SQL » To create a complete hierarchical table from a table with only two columns

Home » SQL & PL/SQL » SQL & PL/SQL » To create a complete hierarchical table from a table with only two columns - parent & child.

Show: Today's Messages :: Show Polls :: Message Navigator
E-mail to friend

To create a complete hierarchical table from a table with only two columns - parent & child. [message #563378]

Mon, 13 August 2012 06:27

panayak
Messages: 1
Registered: August 2012

Junior Member

Hello,

We have a table in the client database that has two columns - column parent and column child. The whole hierarchy of DB table dependencies is held in this table.

If Report 1 is dependent on Table A and Table A in turn is dependent on two tables Table M and Table N. Table N is dependent on table Z it will appear in the db table as,

Hierarchy Table
Parent Child
Report1 Table A
Table A Table M
Table A Table N
Table N Table Z

Requirement :
From the above structure, we need to build a table which will hold the complete hierarchy by breaking it into multiple columns.
The o/p should look like this
Parent Child 1 Child 2 Child 3
Report1 Table A Table M
Report1 Table A Table N Table Z

Child 1, Child 2, Child 3 ....and so on are columns.
The number of tables and the no of hierarchical relationships are dynamic.

SQL Statements to create hierarchy table:

create table hierarchy (parent varchar2(20), child varchar2(20));

insert into hierarchy values ('Report1','Table A');
insert into hierarchy values ('Report1','Table B');
insert into hierarchy values ('Table A','Table M');
insert into hierarchy values ('Table B','Table N');
insert into hierarchy values ('Report2','Table P');
insert into hierarchy values ('Table M','Table X');
insert into hierarchy values ('Table N','Table Y');
insert into hierarchy values ('Report X','Table Z');

Approached already tried :

1) Using indentation :

select lpad(' ',20*(level-1)) || to_char(child) P from hierarchy 
connect_by start with parent='Report1' connect by prior child=parent;

2)Using connect by path function :

select * 
from (select parent,child,level,connect_by_isleaf as leaf, sys_connect_by_path(child,'/') as path 
from hierarchy start with parent='Report1' 
connect by prior child =parent) a where Leaf not in (0);

Both the approaches give the information but the hierarchy data appears in a single column.
Ideally we would like data at each level to appear in a different column.

Thank you.
Pavan
--------------
update, JW: added [code] tags, inserted missing right parenthesis

[Updated on: Sun, 09 September 2012 01:01] by Moderator

Report message to a moderator

Re: To create a complete hierarchical table from a table with only two columns - parent & child. [message #563382 is a reply to message #563378]

Mon, 13 August 2012 07:02

Michel Cadot
Messages: 54201
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Welcome to the forum.

Please read OraFAQ Forum Guide and post your Oracle version as well as your client tool (SQL*PLus? custom program?).

Also please How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.

Regards
Michel

Report message to a moderator

Re: To create a complete hierarchical table from a table with only two columns - parent & child. [message #565936 is a reply to message #563378]

Sat, 08 September 2012 18:07

Barbara Boehmer
Messages: 7668
Registered: November 2002
Location: California, USA

Senior Member

SCOTT@orcl_11gR2> SELECT * FROM hierarchy
  2  /

PARENT               CHILD
-------------------- --------------------
Report1              Table A
Report1              Table B
Table A              Table M
Table B              Table N
Report2              Table P
Table M              Table X
Table N              Table Y
Report X             Table Z

8 rows selected.

SCOTT@orcl_11gR2> COLUMN parent FORMAT A15
SCOTT@orcl_11gR2> COLUMN child1 FORMAT A15
SCOTT@orcl_11gR2> COLUMN child2 FORMAT A15
SCOTT@orcl_11gR2> COLUMN child3 FORMAT A15
SCOTT@orcl_11gR2> COLUMN child4 FORMAT A15
SCOTT@orcl_11gR2> VARIABLE g_ref REFCURSOR
SCOTT@orcl_11gR2> DECLARE
  2    v_sql  VARCHAR2 (32767);
  3    v_ml   NUMBER;
  4  BEGIN
  5    v_sql := 'SELECT parent';
  6    SELECT MAX (LEVEL)
  7    INTO   v_ml
  8    FROM   hierarchy
  9    START  WITH parent = 'Report1'
 10    CONNECT BY PRIOR child = parent;
 11    FOR i IN 1 .. v_ml LOOP
 12  	 v_sql := v_sql ||
 13  	    ', SUBSTR
 14  		 (path,
 15  		  INSTR (path, ''/'', 1,' || i || ') + 1,
 16  		  INSTR (path, ''/'', 1, ' || (i + 1) || ')
 17  		  - INSTR (path, ''/'', 1,' || i || ') - 1) child' || i;
 18    END LOOP;
 19    v_sql := v_sql ||
 20  	 ' FROM   (SELECT CONNECT_BY_ROOT parent AS parent,
 21  			  SYS_CONNECT_BY_PATH (child, ''/'') || ''/'' AS path
 22  		   FROM   hierarchy
 23  		   WHERE  CONNECT_BY_ISLEAF != 0
 24  		   START  WITH parent = ''Report1''
 25  		   CONNECT BY PRIOR child = parent)';
 26    OPEN :g_ref for v_sql;
 27  END;
 28  /

PL/SQL procedure successfully completed.

SCOTT@orcl_11gR2> PRINT g_ref

PARENT          CHILD1          CHILD2          CHILD3
--------------- --------------- --------------- ---------------
Report1         Table A         Table M         Table X
Report1         Table B         Table N         Table Y

2 rows selected.

Report message to a moderator