Re: SELECTing multiple rows to a single output line

From: Barbara Boehmer <baboehme_at_hotmail.com>
Date: 14 Aug 2005 10:01:48 -0700
Message-ID: <1124038908.555588.119630@z14g2000cwz.googlegroups.com>

scott_at_ORA92> CREATE TABLE family

  2    (id	   NUMBER,
  3  	father	   VARCHAR2(7),
  4  	mother	   VARCHAR2(7),
  5  	kids	   NUMBER,
  6  	CONSTRAINT family_pk
  7  		   PRIMARY KEY (id))

  8 /

Table created.

scott_at_ORA92> INSERT ALL
  2 INTO family VALUES (1, 'ALAN', 'ANGIE', 2)   3 INTO family VALUES (2, 'BILL', 'BETTY', 3)   4 SELECT * FROM DUAL
  5 /

2 rows created.

scott_at_ORA92> CREATE TABLE child

  2    (id	   NUMBER,
  3  	child	   VARCHAR2(7),
  4  	CONSTRAINT child_fk
  5  		   FOREIGN KEY (id)
  6  		   REFERENCES family (id))

  7 /

Table created.

scott_at_ORA92> INSERT ALL

  2  INTO child VALUES (1, 'ALEX')
  3  INTO child VALUES (1, 'ANNETTE')
  4  INTO child VALUES (2, 'BARBARA')
  5  INTO child VALUES (2, 'BEVERLY')
  6  INTO child VALUES (2, 'BOB')

  7 SELECT * FROM DUAL
  8 /

5 rows created.

scott_at_ORA92> COMMIT
  2 /

Commit complete.

scott_at_ORA92> SELECT * FROM family
  2 /

        ID FATHER MOTHER KIDS
---------- ------- ------- ----------

         1 ALAN    ANGIE            2
         2 BILL    BETTY            3

scott_at_ORA92> SELECT * FROM child
  2 /

        ID CHILD
---------- -------

         1 ALEX
         1 ANNETTE
         2 BARBARA
         2 BEVERLY
         2 BOB

scott_at_ORA92> COLUMN child_names FORMAT A25 scott_at_ORA92> SELECT id, father, mother, kids,

  2  	    MAX (SUBSTR (SYS_CONNECT_BY_PATH (child, ','), 2))
  3  	      AS child_names
  4  FROM   (SELECT f.id, f.father, f.mother, f.kids, c.child,
  5  		    ROW_NUMBER () OVER
  6  		      (PARTITION BY f.id
  7  		       ORDER BY c.child) AS curr,
  8  		    ROW_NUMBER () OVER
  9  		      (PARTITION BY f.id
 10  		       ORDER BY c.child) - 1 AS prev
 11  	     FROM   family f, child c
 12  	     WHERE  f.id = c.id)

 13 START WITH curr = 1
 14 CONNECT BY PRIOR curr = prev
 15 AND PRIOR id = id
 16 GROUP BY id, father, mother, kids
 17 /

        ID FATHER MOTHER KIDS CHILD_NAMES
---------- ------- ------- ---------- -------------------------

         1 ALAN    ANGIE            2 ALEX,ANNETTE
         2 BILL    BETTY            3 BARBARA,BEVERLY,BOB

scott_at_ORA92> Received on Sun Aug 14 2005 - 12:01:48 CDT