Home » SQL & PL/SQL » SQL & PL/SQL » Transposing Hierarchical Data
Transposing Hierarchical Data [message #258913] Mon, 13 August 2007 15:07 Go to next message
chill
Messages: 4
Registered: August 2007
Junior Member
Dear Experts,

i have a table which represens hierarchical data in my oracle 10g database:
parent_id, child_id

What i want to do is to put out all childs, childs of child etc. of parent_id in one column without using UNION and many joins.

Example:

parent_id child_id
-------------------
10 11
10 12
11 13
13 14

I need this output:

out_column
------
10
11
12
13
14

Is it posssible to do this without UNION and several joins?

Thank you!


[Updated on: Mon, 13 August 2007 15:08]

Report message to a moderator

Re: Transposing Hierarchical Data [message #258914 is a reply to message #258913] Mon, 13 August 2007 15:09 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Why not using union and other conditions?
Is this just a homework?

Regards
Michel
Re: Transposing Hierarchical Data [message #258915 is a reply to message #258913] Mon, 13 August 2007 15:13 Go to previous messageGo to next message
chill
Messages: 4
Registered: August 2007
Junior Member
Hi,
no this is not a homework. I don´t want to use UNION, because i don´t know how deep i have to traverse the tree. The tree depth is between 3 and 9.

Thank you
Re: Transposing Hierarchical Data [message #258917 is a reply to message #258915] Mon, 13 August 2007 15:24 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
But if you just want the list of parents and children you just have to make the union of the 2 columns whatever is the depth.

Regards
Michel

[Updated on: Mon, 13 August 2007 15:24]

Report message to a moderator

Re: Transposing Hierarchical Data [message #258925 is a reply to message #258913] Mon, 13 August 2007 15:41 Go to previous messageGo to next message
chill
Messages: 4
Registered: August 2007
Junior Member
Hmm, this statement:

select parent_id
from tablex a
where a.parent_id = 10
union
select child_id
from tablex a
where a.parent_id = 10
union
select b.child_id
from tablex a
inner join tablex b on a.child_id = b.parent_id
where a.parent_id = 10

gives me only the depth of 2 and not all child results.
How should i write the statement?
Re: Transposing Hierarchical Data [message #258931 is a reply to message #258925] Mon, 13 August 2007 16:18 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Ah ha! Your request was not clear.
You meant not all parents and children but children and so on of ONE parent.
Use CONNECT BY clause.

Regards
Michel
Re: Transposing Hierarchical Data [message #258938 is a reply to message #258931] Mon, 13 August 2007 16:47 Go to previous message
chill
Messages: 4
Registered: August 2007
Junior Member
Thank you,

i got it:

select child_id
from tablex
start with parent_id = 10
connect by prior child_id = parent_id
Previous Topic: sysdate minus 6 hours
Next Topic: SELECT <id> INTO KEY_VAR fails
Goto Forum:
  


Current Time: Wed Dec 07 22:29:08 CST 2016

Total time taken to generate the page: 0.06189 seconds