Home » SQL & PL/SQL » SQL & PL/SQL » SYS_CONNECT_BY_PATH (Oracle 11.1)
SYS_CONNECT_BY_PATH [message #361731] Thu, 27 November 2008 18:19 Go to next message
tmcallister
Messages: 107
Registered: December 2007
Senior Member
Note: this may look like a homework assignment, but it's not - it's just the essentials of work project distilled to the fundamentals

What I want: having data in the following form

MY_ROOT	MY_CHILD
A	a
A	b
A	c
B	d
B	e
C	f
C	g
C	h
C	i


To construct a query that will yield the following

MY_ROOT	MY_PATH
A	a,b,c
B	d,e
C	f,g,h,i


What I have so far can give me any one line, but never all together unless I merge it 3 times with UNIONS, obviously this isn't the desired answer.

SELECT     my_root, SUBSTR (SYS_CONNECT_BY_PATH (my_child, ','), 2)
      FROM (SELECT my_root, my_child,
                   ROW_NUMBER () OVER (PARTITION BY my_root ORDER BY my_child)
                                                                          ord,
                   COUNT (*) OVER (PARTITION BY my_root) cnt
              FROM TEST
             WHERE my_root = 'A')
     WHERE ord = cnt
START WITH ord = 1
CONNECT BY PRIOR ord + 1 = ord  


I'm not certain if what I'm asking is actually possible, due to how START WITH and CONNECT BY work. I'm about to head home for the night and sleep on it. I hope inspiration will strike me.

Here is some code for the test table

create table test
(
    my_root varchar2(8),
    my_child varchar2(8)
);

insert into test values ('A','a');      
insert into test values ('A','b');     
insert into test values ('A','c');    
insert into test values ('B','d');     
insert into test values ('B','e');      
insert into test values ('C','f');      
insert into test values ('C','g');      
insert into test values ('C','h');      
insert into test values ('C','i');


If all else fails I can always iterate through them all one by one in a cursor, but this isn't the desired solution either.

[Updated on: Thu, 27 November 2008 18:21]

Report message to a moderator

Re: SYS_CONNECT_BY_PATH [message #361748 is a reply to message #361731] Thu, 27 November 2008 20:59 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8635
Registered: November 2002
Location: California, USA
Senior Member
SCOTT@orcl_11g> SELECT	my_root,
  2  	     SUBSTR (SYS_CONNECT_BY_PATH (my_child, ','), 2) AS my_path
  3  FROM    (SELECT my_root, my_child,
  4  		     ROW_NUMBER() OVER (PARTITION BY my_root ORDER BY my_child) AS ord,
  5  		     COUNT (*) OVER (PARTITION BY my_root) cnt
  6  	      FROM   test)
  7  WHERE   ord = cnt
  8  START   WITH ord = 1
  9  CONNECT BY PRIOR ord + 1 = ord AND PRIOR my_root = my_root
 10  /

MY_ROOT  MY_PATH
-------- ---------------
A        a,b,c
B        d,e
C        f,g,h,i

SCOTT@orcl_11g>


-- or:

SCOTT@orcl_11g> SELECT	my_root,
  2  	     MAX (SUBSTR (SYS_CONNECT_BY_PATH (my_child, ','), 2)) AS my_path
  3  FROM    (SELECT my_root, my_child,
  4  		     ROW_NUMBER() OVER (PARTITION BY my_root ORDER BY my_child) AS ord
  5  	      FROM   test)
  6  GROUP   BY my_root
  7  START   WITH ord = 1
  8  CONNECT BY PRIOR ord + 1 = ord AND PRIOR my_root = my_root
  9  /

MY_ROOT  MY_PATH
-------- ---------------
A        a,b,c
B        d,e
C        f,g,h,i

SCOTT@orcl_11g>

[Updated on: Thu, 27 November 2008 21:04]

Report message to a moderator

Re: SYS_CONNECT_BY_PATH [message #361919 is a reply to message #361731] Fri, 28 November 2008 10:17 Go to previous message
tmcallister
Messages: 107
Registered: December 2007
Senior Member
Very nice! I did try an 'AND my_root = my_root' addition in the connect by clause, but neglected to add the 'PRIOR' prefix.

I'm especially grateful for the second shortened query, that opens my understanding of how this works a bit.
Previous Topic: trigger to check existence of record and get errors
Next Topic: Query Problem
Goto Forum:
  


Current Time: Wed Dec 07 02:47:19 CST 2016

Total time taken to generate the page: 0.04969 seconds