| 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: query to combine a column
On Jul 16, 8:47 pm, "Tim B" <nos..._at_someisp.ca> wrote:
> "William Robertson" <williamr2..._at_googlemail.com> wrote in message
>
> news:1184564949.710065.230780_at_m3g2000hsh.googlegroups.com...
>
>
>
>
>
> > On Jul 14, 1:44 am, "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?
>
> > Some further suggestions here:
> >http://www.williamrobertson.net/documents/one_row.html
>
> Thanks, there's some useful stuff there.- Hide quoted text -
>
> - Show quoted text -
Apologies if this is a double-post, the first one didn't seem to make it, so here goes:
That does look like an elegant solution for reasonably small datasets. However, its selecting the MAX records of a group of strings - if you run the subquery:
SELECT deptno || ' ' || SYS_CONNECT_BY_PATH(ename,',') AS concatenated
from
( SELECT deptno
, ename
, ROW_NUMBER() OVER (PARTITION BY deptno ORDER BY ename)
AS curr
, ROW_NUMBER() OVER (PARTITION BY deptno ORDER BY ename)
-1 AS prev
FROM emp)
CONNECT BY prev = PRIOR curr AND deptno = PRIOR deptno
START WITH curr = 1
CONCATENATED
10 ,CLARK 10 ,CLARK,KING 10 ,CLARK,KING,MILLER 20 ,ADAMS 20 ,ADAMS,FORD 20 ,ADAMS,FORD,JONES 20 ,ADAMS,FORD,JONES,SCOTT 20 ,ADAMS,FORD,JONES,SCOTT,SMITH 30 ,ALLEN 30 ,ALLEN,BLAKE 30 ,ALLEN,BLAKE,JAMES 30 ,ALLEN,BLAKE,JAMES,MARTIN 30 ,ALLEN,BLAKE,JAMES,MARTIN,TURNER 30 ,ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD
14 rows selected.
... you can see that its taking the maximum strings (based on string length) grouped by the ID number, and that to me seems to be generating and then selecting from a potentially very large intermediate dataset. Received on Tue Jul 17 2007 - 08:09:22 CDT
 |
 |