trees, trees, trees

From: geos <geos_at_nowhere.invalid>
Date: Tue, 19 Jun 2012 23:07:31 +0200
Message-ID: <jrqpna$rcn$1_at_news.task.gda.pl>

there is a table with the following data:

col1 col2
A B
B C
Z A
Z D
M N
M P
X Y

columns col1 and col2 are in parent-child relation. from the above table one can conclude the following three hierarchies:

Z-A-B-C      M       X
  \          / \      |
   D        N   P     Y

the problem: is there a way to get the following result?

col1 col3

A    Z/A/B/C/D
B    Z/A/B/C/D
Z    Z/A/B/C/D
M    M/N/P
X    X/Y

or

col1 col3

A    Z/A/B/C/D
B    Z/A/B/C/D
C    Z/A/B/C/D
D    Z/A/B/C/D
Z    Z/A/B/C/D
M    M/N/P
N    M/N/P
P    M/N/P
X    X/Y
Y    X/Y

the order of concatenated values in col3 is generally irrelevant but all values belonging to a hierachy must be present. to put it in another words: how to consolidate values which form a tree but are not connected with the help of any other common value (like in those examples in oracle documentation about concatenating last names of all employees from department 10 using hierarchical queries)

I would appreciate any suggestion how to do this.

thank you,
geos

ps. this is crosspost but FollowUp-To is set to oracle.misc Received on Tue Jun 19 2012 - 16:07:31 CDT