"Barbara Boehmer" <baboehme_at_hotmail.com> wrote:
> 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
Whoa! This is far more sophisticated SQL than I ever used, so I need
to munch a bit on this to understand all of it and try to apply it for
my actual situation. I might come back for more help though, if you
don't mind.
Thanks a million,
Rudy
Received on Sun Aug 14 2005 - 17:14:16 CDT
