Re: Not sure how to write this in sql
- From: Vladimir Begun <Vladimir.Begun@xxxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Wed, 12 Jan 2005 14:27:39 -0800
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
> --
> //www.freelists.org/webpage/oracle-l
>
--
//www.freelists.org/webpage/oracle-l
Other related posts:
