DBAsupport.com Forums - Powered by vBulletin
Results 1 to 9 of 9

Thread: How to concatenate many rows to be one row?

  1. #1
    Join Date
    Apr 2001
    Location
    indonesia
    Posts
    52

    Question How to concatenate many rows to be one row?

    Hi,

    How can I do if I concatenate two rows or many rows become one row in the same column with different values (CHAR or VARCHAR2). It's possible to do that?

    Ex:
    Column A
    ---------
    ABCD
    EFGH
    ....

    to become

    Column A
    --------
    ABCDEFGH....

    Anybody can help me, please...
    I'ld very appreciated.

    Thanks,

    Ching
    ching

  2. #2
    Join Date
    Oct 2002
    Posts
    182
    use a procedure with a cursor.

    set serveroutput on;

    DECLARE
    v_string_new varchar2(200);

    cursor cGet_String is
    select column_A
    from table_A;
    -- use a where clause here to limit the return

    BEGIN

    v_string_new := '';

    FOR v_string IN cGet_String LOOP

    v_string_new := v_string_new || v_string.column_A;

    END LOOP;

    -- use an insert statement here to throw in a table if you like

    dbms_output.put_line (v_string_new);

    END;
    /
    - Cookies

  3. #3
    Join Date
    Dec 2000
    Location
    Virginia, USA
    Posts
    455
    How about
    select ename || ename from emp;

    you can filter the result by using where clause

  4. #4
    Join Date
    May 2002
    Posts
    2,645
    No, you'll have to do a lot more than filter it. The string concatenation via PL/SQL is a good way to accomplish this. Doing a select whatever||whatever from where_ever deals with one record at a time; hence, the useless results as shown below.

    Code:
    SQL> select ename||ename from emp;
    
    ENAME||ENAME
    --------------------
    SMITHSMITH
    ALLENALLEN
    WARDWARD
    JONESJONES
    MARTINMARTIN
    BLAKEBLAKE
    CLARKCLARK
    SCOTTSCOTT
    KINGKING
    TURNERTURNER
    ADAMSADAMS
    JAMESJAMES
    FORDFORD
    MILLERMILLER
    
    14 rows selected.

  5. #5
    Join Date
    Jan 2003
    Location
    Maryland, USA
    Posts
    12

    How to make this work?

    I tried this procedure. It didn't work. It retuen (1) NOT (2)

    (1) Column_a (2) Column_a
    --------- ---------
    ABC ABCDEF
    DEF

    How to manipulate this? Please give more tips. Thanks
    Frank

  6. #6
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Try this method ...

    drop table my_table;
    create table my_table (my_section number,my_piece number,my_text varchar2(4));
    insert into my_table values (1,1,'ABCD');
    insert into my_table values (1,2,'E');
    insert into my_table values (1,3,'FGH');
    insert into my_table values (1,4,'IJK');
    insert into my_table values (2,1,'LM');
    insert into my_table values (2,2,'NOP');

    select my_section,all_text from
    (
    select
    my_section,
    my_piece,
    my_text||
    lead(my_text,1) over (partition by my_section order by my_piece)||
    lead(my_text,2) over (partition by my_section order by my_piece)||
    lead(my_text,3) over (partition by my_section order by my_piece)||
    lead(my_text,4) over (partition by my_section order by my_piece)||
    lead(my_text,5) over (partition by my_section order by my_piece)||
    lead(my_text,6) over (partition by my_section order by my_piece)||
    lead(my_text,7) over (partition by my_section order by my_piece) all_text,
    first_value(my_piece) over(partition by my_section) first_piece
    from
    my_table
    )
    where my_piece = first_piece
    /

    MY_SECTION ALL_TEXT
    ---------- --------------------------------
    1 ABCDEFGHIJK
    2 LMNOP

    Like most SQL-based methodologies you have to code an upper limit on the number of strings you are going to concatenate, but it has the advantages of ...

    i) not requiring PL/SQL
    ii) being v. fast.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  7. #7
    Join Date
    May 2002
    Posts
    2,645
    Wow, Dave. After Tim Robbins and Susan Sarandon, you are my hero.

  8. #8
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    It's "analyti-tastic"
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  9. #9
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Actually, if you like that, what about ...

    create or replace type AggratinateImpl as object
    (
    concat_string varchar2(4000),
    static function ODCIAggregateInitialize(sctx in out AggratinateImpl) return number,
    member function ODCIAggregateIterate(self in out AggratinateImpl, value in varchar2) return number,
    member function ODCIAggregateTerminate(self in AggratinateImpl, returnValue out varchar2,flags in number) return number,
    member function ODCIAggregateMerge(self in out AggratinateImpl,ctx2 in AggratinateImpl) return number
    );

    create or replace type body AggratinateImpl is
    static function ODCIAggregateInitialize(sctx IN OUT AggratinateImpl)
    return number is
    begin
    sctx := AggratinateImpl('');
    return ODCIConst.Success;
    end;

    member function ODCIAggregateIterate(self IN OUT AggratinateImpl, value IN varchar2)
    return number is
    begin
    self.concat_string := self.concat_string||value;
    return ODCIConst.Success;
    end;

    member function ODCIAggregateTerminate(self IN AggratinateImpl, returnValue OUT
    varchar2, flags IN number) return number is
    begin
    returnValue := self.concat_string;
    return ODCIConst.Success;
    end;

    member function ODCIAggregateMerge(self in out AggratinateImpl,ctx2 in AggratinateImpl) return number
    is
    begin
    self.concat_string := self.concat_string||ctx2.concat_string;
    return ODCIConst.Success;
    end;
    end;
    /

    create or replace function Aggratinate(input varchar2) return varchar2
    aggregate using AggratinateImpl;

    ... then, using the same my_table as before ...

    select my_section,aggratinate(my_text)
    from my_table group by my_section;

    ... or ...

    select my_section,aggratinate(my_text)
    from (select my_section,my_text from my_table order by my_piece desc)
    group by my_section
    /

    you need the create type privilege,and might blow out the varchar2(4000), but hey, it's pretty elegant stuff though i say so myself.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width