Converting multiple rows into a single row 1

[paljnad]

Hello,

I have this query that returns all the valid email addresses from a table on the database:

SELECT distinct trim(user_email_address) || ';'
FROM table a
WHERE active_status <> 0 and user_email_address is not null ;

The records returned are in the following format:

abc@ws.com;
xhj@ws.com;
sd@ws.com;
and so on....

How do I build a text file from this query to build the output to look like abc@ws.com;xhj@ws.com;sd@ws.com;and so on....so that then I could just copy from the text file and paste it into my email.

Thanks.

 

[KenCunningham]

Does this help:

http://dev-notes.com/code.php?q=43

The internet - allowing those who don't know what they're talking about to have their say.

 

[taupirho]

Try this for size.

SQL> select * from tom;

EMAIL
--------------------
tom@mail.com
dick@mail.com
harry@mail.com



SQL> l
1 select substr(max(sys_connect_by_path(email,';')),2) names
2 from
3 (select email,row_number() over (order by rowid) rn
4 from tom
5 )
6 start with rn=1
7* connect by prior rn = rn -1
SQL> /

NAMES
--------------------------------------------------------------------------------
tom@mail.com;dick@mail.com;harry@mail.com




In order to understand recursion, you must first understand recursion.

 

[paljnad]

Thanks, taupirho.

I tried it but it gave me an ORA-01489: result of string concatenation is too long error message. I am pretty sure that the length of the string returned by the select statement is over 4000 characters.

 

[taupirho]

How many records are we taliking about?



In order to understand recursion, you must first understand recursion.

 

[taupirho]

I have to go now, but this worked for me with a table of about 1200 email adresses.

SQL> set long 1000000000
SQL> set pages 10000

SQL> SELECT
xmlagg (xmlelement (c, email || ',')
order by email).extract ('//text()' ) x
FROM tom

X
--------------------------------------------
dick@mail.com;dick@mail.com;dick@mail.com;dick@mail.com;dick@mail.com;dick@
mail.com;dick@mail.com;dick@mail.com;dick@mail.com;dick@mail.com;dick@mail.
com;dick@mail.com;dick@mail.com;dick@mail.com;dick@mail.com;dick@mail.com;d
ick@mail.com;dick@mail.com;dick@mail.com;dick@mail.com;dick@mail.com;dick@m
ail.com;dick@mail.com;dick@mail.com;dick@mail.com;dick@mail.com;dick@mail.c
om;dick@mail.com; etc....


In order to understand recursion, you must first understand recursion.

 

[paljnad]

Thanks again, taupirho.

I was able to modify the query a little bit to get it work and it works beautifully. I am also able to generate a text file output but when I copy and paste from the text file into the 'To' field of my email message then I notice that there are new line characters embedded in between email addresses.

This is what I am using:

set heading off
set feedback off
set echo off
set termspool on
set long 1000000000
set pages 10000
set pagesize 0

But the output looks like this:

a.k@we.com;a.w@we.com;a.f
@we.com;

and when I paste it into the "To" field then it pastes it like this - with the extra ';' at the end of the line:

a.k@we.com;a.w@we.com;a.f;
@we.com;

 

[taupirho]

Add this also

set lines 32767


In order to understand recursion, you must first understand recursion.

 

[paljnad]

Will do.

Thanks for all your help.