Re: query to combine a column

From: dean <deanbrown3d_at_yahoo.com>
Date: Tue, 17 Jul 2007 13:03:53 -0000
Message-ID: <1184677433.221064.29110@d30g2000prg.googlegroups.com>

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 -

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:03:53 CDT