DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 15

Thread: concatenate all rows

  1. #1
    Join Date
    Jun 2005
    Location
    Chennai
    Posts
    6

    Angry concatenate all rows

    Hi
    My question is "how to concatenate all rows as a single row in a column in oracle?".For example consider a table "test" which having 1 column "col1".if the values of the column col is let it be a,b,c,d,e.

    select * from test;
    a
    b
    c
    d
    e

    I need the output in the folowing format.
    abcde.
    Please help me.Thanks in Advance.

    Thanks & Regards
    RajendranR

  2. #2
    Join Date
    Sep 2003
    Location
    india
    Posts
    23
    hi,

    U can display it through utl_file.......
    ramya

  3. #3
    Join Date
    May 2005
    Posts
    10
    You can write your own function to do that :

    SQL> create or replace function my_func return varchar2 is
    2 type rc is ref cursor;
    3 v_str varchar2(20);
    4 v_str_tmp varchar2(20);
    5 c rc;
    6 begin
    7 open c for 'select * from test';
    8 loop
    9 fetch c into v_str_tmp;
    10 exit when c%notfound;
    11 v_str := v_str||v_str_tmp;
    12 end loop;
    13 return v_str;
    14 end;
    15 /

    Function created.

    SQL> select my_func() from test where rownum < 2;

    MY_FUNC()
    ----------
    abcde


    Hope this helps.

  4. #4
    Join Date
    Jun 2005
    Location
    Chennai
    Posts
    6
    Hi
    First Thanks for ur reply.I need say one thing. I shoul not use function or procedure at all. i hve to do it by a single query.
    Thanks
    Rajendran

  5. #5
    Join Date
    Jan 2002
    Location
    Up s**t creek
    Posts
    1,525
    How about

    Code:
    SQL> select * from t1;
    COL1
    ----
    a
    b
    c
    d
    e
    
    SQL> set serveroutput on
    SQL> declare
      2>    v_str varchar2(100);
      3>  begin
      4>    for c in (select col1 from t1) loop
      5>      v_str := v_str||c.col1;
      6>    end loop;
      7>    dbms_output.put_line(v_str);
      8> end;
      9> /
    abcde
    HTH
    Jim
    Oracle Certified Professional
    "Build your reputation by helping other people build theirs."

    "Sarcasm may be the lowest form of wit but its still funny"

    Click HERE to vist my website!

  6. #6
    Join Date
    Jun 2005
    Location
    Chennai
    Posts
    6
    Hi Jim
    Thanks for your reply.But i need to do this one in a single query.not using user defined function,user defined procedure.Thanks
    for help.

    Thanks
    Rajendran

  7. #7
    Join Date
    May 2005
    Location
    Toronto Canada
    Posts
    57
    Ordinarily, I would suggest searching Ask Tom for stragg. However, since you cannot or will not use the best approach.

    This works for your supplied data set, and may be generalizable to other data sets, depending on the real structure of you data. Also, it requires Oracle 9.

    Code:
    SQL> SELECT * FROM t;
     
    COL1
    --------------------
    a
    b
    c
    d
    e
     
    SQL> SELECT REPLACE(col1, ' ')
      2  FROM (SELECT SYS_CONNECT_BY_PATH(col1,' ') col1, level
      3        FROM t
      4        START WITH col1 = 'a'
      5        CONNECT BY PRIOR col1 < col1
      6        ORDER BY level DESC)
      7  WHERE rownum = 1;
     
    REPLACE(COL1,'')
    ------------------
    abcde
    HTH
    John

  8. #8
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    Thanks John.

    Slight variation of John's SQL statement.

    PHP Code:
      1   SELECT REPLACE(col1' ')
      
    2      FROM (SELECT SYS_CONNECT_BY_PATH(col1,' 'col1level
      3            FROM t1
      4            START WITH col1 
    = (select min(col1from t1) -- 'a'
      
    5            CONNECT BY PRIOR col1 col1
      6            ORDER BY level DESC
    )
      
    7*     WHERE rownum 1
    SQL
    > /

    REPLACE(COL1,'')
    ----------------------------------------
    abcd 
    Tamil

  9. #9
    Join Date
    May 2005
    Location
    Toronto Canada
    Posts
    57
    Tamil:

    Thanks, I was testing with hard coded values to make sure the concept worked, and missed that generalization.

    John

  10. #10
    Join Date
    Jun 2005
    Location
    Chennai
    Posts
    6

    Thanks

    Hi
    This Query is working fine.Thanks a lot for your response.

    Thanks
    RajendranR

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