FAQ
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

Search Discussions

  • Reidy, Ron at Jan 12, 2005 at 4:04 pm
    Chris,

    Can you do it in PL/SQL using dbms_utilty.comma_to_table and =
    dbms_utility.table_to_comma to get/insert data in your table? Or maybe =
    do some bulk processing in PL/SQL?

    --
    Ron Reidy
    Lead DBA
    Array BioPharma, Inc.

    -----Original Message-----

    From: oracle-l-bounce_at_freelists.org on behalf of Chris Stephens
    Sent: Wed 1/12/2005 2:51 PM
    To: oracle-l_at_freelists.org
    Cc:=09
    Subject: Not sure how to write this in sql

    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
    =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
    id col2
    1 a
    1 b
    1 c

    should populate table b like

    table b
    =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
    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

    This electronic message transmission is a PRIVATE communication which =
    contains
    information which may be confidential or privileged. The information is =
    intended=20
    to be for the use of the individual or entity named above. If you are =
    not the=20
    intended recipient, please be aware that any disclosure, copying, =
    distribution=20
    or use of the contents of this information is prohibited. Please notify =
    the
    sender of the delivery error by replying to this message, or notify us =
    by
    telephone (877-633-2436, ext. 0), and then delete it from your system.

    --
    http://www.freelists.org/webpage/oracle-l
  • David wendelken at Jan 12, 2005 at 4:24 pm
    Always think in terms of sets of data!

    This code makes several assumptions, which may not be safe ones.
    But you can adjust this to handle things appropriately.

    Never More than 3 rows.
    (change the decode logic into a loop in a function
    and pass it the counter, return the string
    if not a safe assumption.
    And it is almost certainly NOT a safe one!
    )
    Not already in table_b.
    (use the merge command instead of the insert command)
    Don't care if table_a no longer has the record.
    (You'll need to consider deletes of ids in table_b that are no longer in table_a if you are merging).

    But this should give you the idea:

    insert into table_b b
    (id, col2)
    select id, decode(counter,1,'1',2,'1,2',3,'1,2,3',NULL)
    from
    (select
    a.id, count(a.id) counter
    from table_a a
    group by a.id
    )
  • Vladimir Begun at Jan 12, 2005 at 4:28 pm
    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
  • Vladimir Begun at Jan 12, 2005 at 4:46 pm

    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, RTRIM(
    MAX(DECODE(rn, 1, value))
    ','
    MAX(DECODE(rn, 2, value))
    ','
    MAX(DECODE(rn, 3, value))
    ','
    MAX(DECODE(rn, 4, value)), ','
    ) result
    FROM (
    SELECT ROW_NUMBER() OVER (PARTITION BY grp ORDER BY value) rn, grp, value
    FROM t
    )

    GROUP BY grp
    /

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouporacle-l @
categoriesoracle
postedJan 12, '05 at 3:53p
activeJan 12, '05 at 4:46p
posts5
users4
websiteoracle.com

People

Translate

site design / logo © 2022 Grokbase