asked on

Sql statement to concatenate different rows

I have a query which returns multiple rows for a column. Is there any way to concatenate the rows in sql statement without creating a procedure.

Here is an example

Select col1 from table1 where col2 = 'XXXX' order by col3, col4

result set is

ABCDE
FGHIJ
KLMN
.....
.....
The number of rows can vary from 0 to 10
I need to write a view which should return one row with

ABCDEFGHIJKLMN.......

catchmeifuwant

Nothing that I can imagine...

SOLUTION

ToddBarry

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER CERTIFIED SOLUTION

paquicuba

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

dnabatra

ASKER

Solution from ToddBarry does not work in Oracle 8i. But seems like a good solution.

Solution from paquicuba works fine upto 4000 characters, but if it is more than 4000 charcaters, it does not work. I get the following error.
(Error): ORA-01489: result of string concatenation is too long

This concatemnation can have upto 20,000 characters.

Any way to increase the size?

ToddBarry

dnabatra, if you are running a version of Oracle that is two versions old and released six years ago, you need to state that in your question, otherwise it is a waste of time to provide a solution that works in all recent version of Oracle but not in your old version.

dnabatra

ASKER

I will in future...

But the answer is useful because i have to do the samething 9i after a month or so

Thanks