Re: query to combine a column

"dean" <deanbrown3d_at_yahoo.com> wrote in message news:1184556664.628465.55500_at_n60g2000hse.googlegroups.com...
> 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 -

>

Here it is. I've actually used lag in this example. This works, but there are probably a cleaner and simpler ways to do it, maybe without 2 subqueries I'm not planning to do much refinement on this, since I've worked out a way to achieve the same result using Java in the web app I'm working on, only without any concern about upper limits, and no need to split up concatenated strings, so I'll be going with the Java solution.

with T as (
select '123' id, 'dingo' description, 'stuff123' col3 from dual

union

select '357' id, 'anteater' description, 'stuff357' col3 from dual

union

select '357' id, 'aardvark' description, 'stuff357' col3 from dual

union

select '357' id, 'wombat' description, 'stuff357' col3 from dual

union

select '677' id, 'kangaroo' description, 'stuff677' col3 from dual

)

select id, descr, col3 from (

select id, descr, col3, RANK() OVER (PARTITION BY id ORDER BY length(descr) DESC) AS myrank from (

select id,

description

|| nvl2((lag(description, 1 ) over (partition by id order by description)), ('$$$'||lag(description, 1 ) over (partition by id order by description)), '')

|| nvl2((lag(description, 2 ) over (partition by id order by description)), ('$$$'||lag(description, 2 ) over (partition by id order by description)), '')

|| nvl2((lag(description, 3 ) over (partition by id order by description)), ('$$$'||lag(description, 3 ) over (partition by id order by description)), '')

|| nvl2((lag(description, 4 ) over (partition by id order by description)), ('$$$'||lag(description, 4 ) over (partition by id order by description)), '')

|| nvl2((lag(description, 5 ) over (partition by id order by description)), ('$$$'||lag(description, 5 ) over (partition by id order by description)), '')

|| nvl2((lag(description, 6 ) over (partition by id order by description)), ('$$$'||lag(description, 6 ) over (partition by id order by description)), '')

|| nvl2((lag(description, 7 ) over (partition by id order by description)), ('$$$'||lag(description, 7 ) over (partition by id order by description)), '')

|| nvl2((lag(description, 8 ) over (partition by id order by description)), ('$$$'||lag(description, 8 ) over (partition by id order by description)), '')

|| nvl2((lag(description, 9 ) over (partition by id order by description)), ('$$$'||lag(description, 9 ) over (partition by id order by description)), '')

|| nvl2((lag(description, 10 ) over (partition by id order by description)), ('$$$'||lag(description, 10 ) over (partition by id order by description)), '')

descr , col3

from T) )

where myrank = 1

Tim B Received on Sun Jul 15 2007 - 23:22:31 CDT