comma delimited list

Hello,
I have created a small query that will output a list of email addresses that
are separated by commas. I plan to copy and paste the list into a email
invite field (send to: field for an email app). But what is happening is tha
t
only one address appears in the field when I paste because of the added
spaces between the names, example currently outputs like:
blahblah@.blah.com,
blahblah@.blah.com,
blahblah@.blah.com
What I need is it to output like this:
blahblah@.blah.com,blahblah@.blah.com,blahblah@.blah.com
Here is the small query that I am using to extract the emails.
SELECT RTRIM(LTRIM(EMAIL_ADDRESS)) + ',' AS [Name]
FROM evPRUclinicala
WHERE CREATE_DATE BETWEEN '4/1/06' AND '4/30/06'
AND EMAIL_ADDRESS IS NOT NULL
Any help would be appreciated and thanks in advance.
Message posted via http://www.webservertalk.comIn general, a recommended approach is to extract the resultset outside the
server and massage the data to appropriate display format using some client.
Regarding the alterantives for doing it at the server, you can check out the
following links:
( For SQL 2005 only )
http://groups.google.com/group/micr...br />
9b9b968a
( For SQL 2000 & 2005 )
http://groups.google.com/group/micr...br />
6dd9e73e
Anith|||Anith Sen wrote:
>In general, a recommended approach is to extract the resultset outside the
>server and massage the data to appropriate display format using some client
.
>Regarding the alterantives for doing it at the server, you can check out th
e
>following links:
>( For SQL 2005 only )
>http://groups.google.com/group/micr...r />
a9b9b968a
>( For SQL 2000 & 2005 )
>http://groups.google.com/group/micr...r />
66dd9e73e
>
Thanks for the direction, but I am still have some problems. I elected to us
e
the cursor version of your solution: see below
DECLARE @.temptable TABLE ( list VARCHAR( 8000 ) )
SET NOCOUNT ON
DECLARE @.PRUemail VARCHAR( 1000 ), @.emailNext VARCHAR (100)
DECLARE c CURSOR FOR
SELECT EMAIL_ADDRESS
FROM evPRUclinicala
ORDER BY EMAIL_ADDRESS
OPEN c
FETCH NEXT FROM c INTO @.emailNext
SET @.PRUemail = @.emailNext
WHILE @.@.FETCH_STATUS = 0 BEGIN
IF @.emailNext > @.PRUemail BEGIN
INSERT @.temptable SELECT @.PRUemail
SELECT @.PRUemail = @.emailNext
END ELSE
SET @.PRUemail = COALESCE( @.PRUemail + ',', SPACE( 0 ) ) +
@.emailNext
FETCH NEXT FROM c INTO @.emailNext
END
INSERT @.temptable SELECT @.PRUemail
CLOSE c
DEALLOCATE c
SET NOCOUNT OFF
SELECT * FROM @.temptable
But I am still getting a vertical list with no commas. Can you tell me what
I
am doing wrong? Thanks
Message posted via http://www.webservertalk.com|||>> I elected to use the cursor version of your solution:
Why? It is a bad choice to begin with.
I have no idea how the table evPRUclinicala is structured or what the nature
of the data is. Can you post your table DDLs & a few sample data? For
details, refer to: www.aspfaq.com/5006
Anith|||"Jay via webservertalk.com" <u7124@.uwe> wrote in message
news:601bd1f4c21b5@.uwe...
>.
> Can you tell me what I am doing wrong?
>.
Yeah, your not using Rac :)
www.rac4sql.net/onlinehelp.asp?topic=236|||Anith Sen wrote:
>Why? It is a bad choice to begin with.
>
>I have no idea how the table evPRUclinicala is structured or what the natur
e
>of the data is. Can you post your table DDLs & a few sample data? For
>details, refer to: www.aspfaq.com/5006
>
What would have been a better choice? If I should use a different method tha
n
I would appreciate your advise here. If not I will get you the DDLs.
Message posted via webservertalk.com
http://www.webservertalk.com/Uwe/Forum...amming/200605/1|||Cursors are generally performance hogs. In most cases, it is wiser to
utilize a set based solution in SQL where a set of rows are manipulated as a
whole rather than doing it one row at a time.
As a long term solution, consider returning the resultset to the client and
manipulate the data using a client application program -- this allows the
stored procedure to be sufficiently generic, eliminates the need to use
server's processing power for string manipulations & loop-based constructs
and leverages the string functionalities of the client language, which in
most cases are more versatile than t-SQL.
If you are convinced to use the server for such stuff, consider the table
valued UDF approach which might be better performing for medium sized
datasets. For SQL 2005, the XML kludge might be faster, though we do not
have much useful benchmarks with this approach.
Alternatively, for large volume transactions of this nature, consider using
3rd party tool's like RAC mentioned in Steve's post.
Anith|||Anith Sen wrote:
>Why? It is a bad choice to begin with.
>
>I have no idea how the table evPRUclinicala is structured or what the natur
e
>of the data is. Can you post your table DDLs & a few sample data? For
>details, refer to: www.aspfaq.com/5006
>
Ok I will look at the other solutions rather than the one chosen. Thanks
Message posted via webservertalk.com
http://www.webservertalk.com/Uwe/Forum...amming/200605/1

comma deliminited coloumn

I have two tables. One table (Table 1) has a column containing comma
delaminated email addresses. The other table (Table 2) has a column
with just one email address in it. I need to perform a query that
joins the comma delaminated table (Table 1) to Table 2, when the single
email address in Table 2, is contained in the list of email addresses
in (Table 1).

I hope this isn't too cryptic, and I know comma delaminated lists are
bad, but I can't do anything about that.

I need a select statement that can perform this task. Anyone have any
suggestions?--BEGIN PGP SIGNED MESSAGE--
Hash: SHA1

Set the WHERE clause like this:

WHERE table1.columnA like '%' + table2.columnA + '%'

BTW, it's "delimited" not "delaminated." Delaminated means to remove
the lamination (thin layers of some material) from a surface.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

--BEGIN PGP SIGNATURE--
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBRAYMHoechKqOuFEgEQLfUgCgiFts2PFVLGIgVtIozCwnTc AinqsAoOIg
GOmdcAyO5uy641wMCQWwt4Sj
=3bUQ
--END PGP SIGNATURE--

cholmqui@.gmail.com wrote:
> I have two tables. One table (Table 1) has a column containing comma
> delaminated email addresses. The other table (Table 2) has a column
> with just one email address in it. I need to perform a query that
> joins the comma delaminated table (Table 1) to Table 2, when the single
> email address in Table 2, is contained in the list of email addresses
> in (Table 1).
> I hope this isn't too cryptic, and I know comma delaminated lists are
> bad, but I can't do anything about that.
> I need a select statement that can perform this task. Anyone have any
> suggestions?|||Thank you for the reply.

still doesn't work...

Sorry about the lamination, I kinda flew through the spell checker|||(cholmqui@.gmail.com) writes:
> Thank you for the reply.
> still doesn't work...

MGFosters looks good to me at a glance. Maybe you could be more specfic
to what does not work? Even better, provide CREATE TABLE statments and
INSERT statements with sample data. Then you can get a tested solution.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||This design seems to suck. You need some procedural routines to scrub
data, You need to look at Melisa data's website.