Re: Not sure how to write this in sql

From: Vladimir Begun <Vladimir.Begun_at_oracle.com>
Date: Wed, 12 Jan 2005 14:27:39 -0800
Message-ID: <41E5A45B.6050705@oracle.com>

Chris

Please consider this:

DROP TABLE t;
CREATE TABLE t (grp NUMBER, value VARCHAR2(10));

INSERT INTO t VALUES(1, 'a');
INSERT INTO t VALUES(1, 'b');
INSERT INTO t VALUES(1, 'c');
INSERT INTO t VALUES(2, 'd');
INSERT INTO t VALUES(2, 'e');

COMMIT; SELECT grp, SUBSTR(SYS_CONNECT_BY_PATH(value, ','), 2) concat

   FROM (

        SELECT grp, value, COUNT(*) OVER (PARTITION BY grp) cnt
          FROM t
        )

  WHERE LEVEL = cnt
CONNECT BY PRIOR grp = grp

    AND PRIOR value < value
/

This can be very expensive, therefore one can use UDAF:

Oracle9i Data Cartridge Developer's Guide Release 2 (9.2), 11 "User-Defined Aggregate Functions"

-- 
Vladimir Begun
The statements and opinions expressed here are my own and
do not necessarily represent those of Oracle Corporation.

Chris Stephens wrote:



> We have a function  that loops through a table and grabs all rows

> associated with a particular id and builds a string consisting of a

> comma delimited, concatenate string of 1-3 rows of a column associated

> with that id.

> 

> the function returns the string and that string is used to populate a

> column in another table.

> 

> i believe this is possible in a single update statement but i can't

> figure out how to right it. (as it is, it takes over an hour)

> 

> so:

> 

> table a

> ==============

> id  col2

> 1    a

> 1    b

> 1    c

> 

> should populate table b like

> 

> table b

> ==============

> id  col2

> 1   1,2,3

> 

> table a can have 0,1,2, or 3 rows associated with each id.

> 

> help?

> 

> ps.  I have lex's book on order so hopefully my sql skills will

> continue to grow.

> 

> tia

> chris

> --

> http://www.freelists.org/webpage/oracle-l

> 


--
http://www.freelists.org/webpage/oracle-l

Received on Wed Jan 12 2005 - 16:28:00 CST