| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: How do we select multiple return rows on a single line?
On Thu, 03 Dec 1998 21:29:38 GMT, ed_at_kideys.com wrote:
>I would like to assemble a select query that lists all of the returned data
>in a single line - suppressing the carriage returns between them. For use
>essentially as a "field" in a larger parent query.
>
>For example, if I have a table "parent (name, age, sex)", and a seperate table
>"child (name, parent name)".
>I do not know how many children a given parent has (zero to N).
>
>I'd like the report output to look something like:
>
>Parent Name Age Sex Children
>----------- --- --- --------
>Fred 32 M Sue, Betsy
>Karen 40 F Tom
>Bob 15 M
>Mark 29 M Abe, Bill, Cindy
>
>But I do not know how to write the query such that all the children can be
>returned in a single "field" of the parent query.
>
>All of my attempts at this thus far has resulted in one child per row
>returned, with the parent data on each line - which really isn't what I'm
>looking for at all.
You could write a function that returns the all children for a given parent and select that in you parent query.
eg.
create or replace
function get_children( p_parent varchar2 ) return varchar2 is
l_children long := null;
begin
for c in ( select decode( rownum, 1, name, ', '||name ) name
from child
where parent = p_parent ) loop
l_children := l_children || c.name;
end loop;
Now your query will be
select name "Parent Name",
age "Age",
sex "Sex",
get_children( name ) "Children"
hope this helps.
chris.
>
>Ed Kideys
>
>-----------== Posted via Deja News, The Discussion Network ==----------
>http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own
--
Chirstopher Beck
Oracle Corporation
clbeck_at_us.oracle.com
Reston, VA.
 |
 |