1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

SQL question using 10g.2 on Windows: How to concatenate many strings into one.

Discussion in 'General' started by uninformed1, Jun 9, 2014.

  1. uninformed1

    uninformed1 Guest

    Two tables: Account table; Account Notes table.

    Many Account Note records (varchar2(40)) belonging to a single Account record must be concatenated into one long string in an extract record to be loaded into a clob field belonging to a target table.

    Using SQL only, how can this be done? If it cannot, any idea on a PL/SQL function called from SQL?

    Thanks
     
  2. rajenb

    rajenb Forum Expert

    Messages:
    361
    Likes Received:
    114
    Trophy Points:
    655
    Location:
    Mauritius
    In 10g, it would be simpler to write a function - try the following:

    Code (SQL):
    CREATE TABLE ACCOUNT
    ( id NUMBER
    );
    CREATE TABLE account_notes
    ( account_id NUMBER,
      notes VARCHAR2(40)
    );

    INSERT INTO account VALUES (1);
    INSERT INTO account VALUES (2);

    INSERT INTO account_notes VALUES (1, 'AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA');
    INSERT INTO account_notes VALUES (1, '11111111111111111111111111');
    INSERT INTO account_notes VALUES (2, 'BBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBB');
    INSERT INTO account_notes VALUES (2, '22222222222222222222222222222222222222');

    CREATE OR REPLACE
      FUNCTION f_aggregate(
          p_account_id IN NUMBER)
        RETURN VARCHAR2
      IS
        l_str VARCHAR2(4000) := NULL;
      BEGIN
        FOR cur_rec IN
        (SELECT an.notes FROM account_notes an WHERE an.account_id = p_account_id
        )
        LOOP
          l_str := l_str || cur_rec.notes;
        END LOOP;
        RETURN (l_str);
      END;
      /

      SELECT id, f_aggregate(id) FROM account ORDER BY id;

    --Output:
        ID
    ----------
    NOTES
    --------------------------------------------------------------------------------
         1
    11111111111111111111111111AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA

         2
    22222222222222222222222222222222222222BBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBB
    P.S: In 11g, you have a standard Oracle function which can deliver same results:

    Code (SQL):
    SELECT a.id,
      LISTAGG(an.notes) WITHIN GROUP (
    ORDER BY id) AS notes
    FROM account_notes an,
      account a
    WHERE an.account_id = a.id
    GROUP BY A.ID;
     
  3. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation