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

hi,

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

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.

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

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

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

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

Thanks John.

Slight variation of John's SQL statement.

PHP Code:

---

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

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


---

Tamil

Tamil:

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

John

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

Thanks
RajendranR

Quote:

---

select min(col1) from t1

---

This does not guarantee the first row.How about this?

Quote:

---

select col1 from t1 where rownum=1

---

Quote:

---

Originally posted by simply_dba This does not guarantee the first row.How about this? select col1 from t1 where rownum=1

---

In database there is no concept of first row, last row etc... If you are inserting records with timestamp or with sequence only then you can get the first row inserted or last row inserted.

In 8k blocksize database try this

create table test (a char(2000), b char(2000), c char(2000), d char(2000));

insert into test values ('1','b','c','d');
insert into test values ('2','b','c','d');
insert into test values ('3','b','c','d');

select a from test where rownum=1; -- what is value of a shown by this query?

delete from test where a='1';

commit;

insert into test values ('4','b','c','d');

select a from test where rownum=1; -- what is value of a now shown by this query?

Yes, but the statement is more likely to fetch the answer the original poster wanted than using min()

Quote:

---

Originally Posted by simply_dba

Yes, but the statement is more likely to fetch the answer the original poster wanted than using min()

---

WHERE ROWNUM = 1 means get me one random row from the query. The row returned can and will change for any number of reasons. Bonker demonstrated one way (delete/insert), changing access paths is another (e.g. Oracle decides to use an index instead of a full scan). Another reason would be simply order of insertion.

Code:

---

SQL> CREATE TABLE t (col1 VARCHAR2(1));
 
Table created.
 
SQL> INSERT INTO t VALUES ('e');
 
1 row created.
 
SQL> INSERT INTO t VALUES ('d');
 
1 row created.
 
SQL> INSERT INTO t VALUES ('c');
 
1 row created.
 
SQL> INSERT INTO t VALUES ('b');
 
1 row created.
 
SQL> INSERT INTO t VALUES ('a');
 
1 row created.
 
SQL> COMMIT;
 
Commit complete.

SQL> SELECT REPLACE(col1, ' ')
  2  FROM (SELECT SYS_CONNECT_BY_PATH(col1,' ') col1, level
  3        FROM t
  4        START WITH col1 = (SELECT MIN(col1) FROM t)
  5        CONNECT BY PRIOR col1 < col1
  6        ORDER BY level DESC)
  7        WHERE rownum = 1;
 
REPLACE(COL1,'')
-----------------
abcde
 
SQL> SELECT REPLACE(col1, ' ')
  2  FROM (SELECT SYS_CONNECT_BY_PATH(col1,' ') col1, level
  3        FROM t
  4        START WITH col1 = (SELECT col1 FROM t WHERE rownum = 1)
  5        CONNECT BY PRIOR col1 < col1
  6        ORDER BY level DESC)
  7        WHERE rownum = 1
 
REPLACE(COL1,'')
-----------------
e

---

So, which one is more likely to be correct?

TTFN
John

I have the following table and data:

CREATE TABLE t1 (col1 VARCHAR2(1), col2 VARCHAR2(25));
INSERT INTO t1 (col1,col2) VALUES ('1','TEST1');
INSERT INTO t1 (col1,col2) VALUES ('2','TEST1');
INSERT INTO t1 (col1,col2) VALUES ('5','TEST2');
INSERT INTO t1 (col1,col2) VALUES ('9','TEST2');
INSERT INTO t1 (col1,col2) VALUES ('1','TEST3');
INSERT INTO t1 (col1,col2) VALUES ('1','TEST4');
INSERT INTO t1 (col1,col2) VALUES ('5','TEST5');


I would like to get the following results from a single query:

co1 col2
TEST1 1,2
TEST2 5,9
TEST3 1
TEST4 1
TEST5 5


If anyone knows how to do this using connect by i would appreciate your help. Thanks