Re: SELECTing multiple rows to a single output line

I know you asked for a select statement without a procedure or function, so that is was I provided. However, ideally you should get your DBA to stop "unstoring" things during "maintenance", then you could use something like Tom Kyte's stragg user-defined aggregate function:

http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:2196162600402

and your code would be as simple as:

scott_at_ORA92> SELECT f.id, f.father, f.mother, f.kids,   2 stragg (c.child) AS child_names   3 FROM family f, child c
  4 WHERE f.id = c.id
  5 GROUP BY f.id, f.father, f.mother, f.kids   6 /

        ID FATHER MOTHER KIDS CHILD_NAMES

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

scott_at_ORA92>

All you need to do is copy his code, remove the line numbers, run it to create the type and function, then you can use it as demonstrated above in any similar situation. You do not need to write separate code for each such circumstance. Received on Sun Aug 14 2005 - 22:46:06 CDT