OracleBlog

I couldn't resist sharing this one, which I found on DBA-Support recently:

http://www.dbasupport.com/forums/showthread.php?t=47760

The question was how to create a column as the concatentation of many rows into one, without using PL/SQL: just a single query.

Here is the example given:

CREATE TABLE t1 (col1 VARCHAR2(1));
INSERT INTO t1 VALUES ('a');
INSERT INTO t1 VALUES ('b');
INSERT INTO t1 VALUES ('c');
INSERT INTO t1 VALUES ('d');
INSERT INTO t1 VALUES ('e');

Desired result of an SQL statement on test:

abcde

The answer came from Tamil Selvan:

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,'')
----------------------------------------
abcde

Here are the things that may be new to someone new to Oracle (especially Oracle 9 or 10):
- SYS_CONNECT_BY_PATH
- CONNECT BY

This is an interesting example, but it is just one use of these features.

Here is a link to Dan Morgan's reference on CONNECT BY
http://www.psoug.org/reference/connectby.html

And here is an Ask Tom article.
http://www.oracle.com/technology/oramag/oracle/05-may/o35asktom.html