Re: SELECTing multiple rows to a single output line

"Barbara Boehmer" <baboehme_at_hotmail.com> wrote:
>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.

   Thanks for the additional help, Barbara. Unfortunately, in the past we often discovered a dropped stored procedure when the DBA was not around for immediate help and he was the only one authorized to do that. With dynamic SQL we have to worry less about such things.

   In the meantime I tried to apply your technique for my situation here but I encountered a snag when I had to SELECT columns with DISTINCT values. The situation could be modelled with the following simple Bill-Of-Material (BOM) example:

Assume that I am making several widgets, each made of two or more components and each component from several different parts. Some of the components are used in more than one widget and the same parts may be used in more than one component. What I am interested in is getting a unique list of parts for each widget where the part count is under some number, in a comma separated fashion, as the children were listed for each parent. How can your method be adjusted to list such "grandchildren" without duplicating any of them for a given "grandparent?" I tried just about everything I could think of but I'm afraid my knowledge of advanced SQL features is no match for the task.

Thanks again,
Rudy Received on Mon Aug 15 2005 - 19:49:46 CDT