On Jul 15, 2:25 am, "Tim B" <nos..._at_someisp.ca> wrote:
> "dean" <deanbrow..._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- Hide quoted text -
>
> - Show quoted text -
Good - can you post the query here for reference? Would like to see
another take on this.
Received on Sun Jul 15 2007 - 22:31:04 CDT
