Re: get multiple rows to columns

Looks like you answered your own question: IN A STORED PROCEDURE. (hint: look up cursors in PL/SQL)

kenneth <kenneth_at_hotmail.com> wrote in message news:<phfr9u4j9fn36ikmac2q0udhsvgo7emqro_at_news-west.newscene.com>...
> Hi,
> I'm new to Oracle
>
> I have the following problem to solve in a store procedure that passes
> its output to a crystal report.
> It's probably a generic problem
>
> I have table 1 containing patients surgery data and table 2 the drugs
> used. The key they are joined is the surgeryid.
> The drug table has one line for every drug used. For one surgeryid I
> can get up to 9 records.
> If I just join on the surgeryid I get every surgery case more than
> once, one for every drug used.
>
> the output I get now
> patient 1 surgery therapy drug1
> patient 1 surgery therapy drug2
> patient 1 surgery therapy drug3
> patient 2 surgery therapy drug1
> patient 2 surgery therapy drug2
>
> .....
>
> but I want to have an output that looks like this
>
> patient1 surgery, therapy, drug1,drug2,drug3
> patient2.....
>
>
> of course if there's just one drug used I don't want to have
> "drug1,,,,,, " in the report etc.
>
> system used is oracle 8.0.5
>
> How do I get the multiple sets in table 2 in one column (or will I
> need up to 9) in the temp table used in the report.
>
>
>
>
> thanks
>
> kenneth
>
> PS:
> sql and english are not my native languages :-))
Received on Mon Mar 25 2002 - 23:37:24 CET