Re: query to combine a column

"dean" <deanbrown3d_at_yahoo.com> wrote in message news:1184386627.641945.84690_at_q75g2000hsh.googlegroups.com...
> On Jul 13, 8:44 pm, "Tim B" <nos..._at_someisp.ca> wrote:
> > I have a query that returns something like this, which is in a pl/sql
> > function:
> >
> > id description col3
> >
> > ---------------------------------
> > 123 dingo stuff123
> >
> > 357 anteater stuff357
> >
> > 357 aardvark stuff357
> >
> > 357 wombat stuff357
> >
> > 677 kangaroo stuff677
> >
> > What I want is a query that will transform the results of the above
query
> > like this:
> >
> > id description col3
> >
> > -------------------------------------------------------------
> > 123 dingo stuff123
> >
> > 357 anteater$$aardvark$$wombat stuff357
> >
> > 677 kangaroo stuff677
> >
> > I want to remove the duplicate case_nums and combine their descriptions.
The
> > '$$' would be for use as a marker for splitting up the string inJava.
> >
> > Any suggestions on how to do this, if it can be done?
> >
> > Alternatively - This query is used to populate a cursor, which is
returned
> > by the function.
> >
> > Is there a way to make the transformation in pl/sql and still return a
> > cursor from the function?
>
> Is there an upper limit of the number of records with the same ID?
> Are you ordering them alphabetically within the group (because it
> appears you are not, despite using aardvark as an example).
>
> If there IS an upper limit (within reason, let's say 100), then a
> simple query using various ranking functions will give you the
> results. Look into the LEAD function, passing in 1,2,3,4,5.....100 as
> the second parameter to retrieve data in the correct order and
> grouping.
>

Thanks for the suggestions and examples, Dean. In theory, there is no upper limit, but in reality the number would should go over 100, and even if it did, there would be no significant issues. And you are correct, ordering alphabetically within the group is not required. I've worked it out using lead, partition, and ranking functions.

Tim B Received on Sun Jul 15 2007 - 01:25:31 CDT