x

Email list in T-SQL

I have a sql task in a ssis package loops through each customer and emails a file. So for every iterateion I need to create a list of To Recipients, CC, and BCC with the semicolon between the addresses. I was trying a case statement but I do not know if that will work?

So For Every ONE Customer, I want to create one TO, CC, and BCC Columns which gets passed to a pckage variable - then used when sending email.

SELECT  HDR.CUST_NUM,
       HDR.CUST_NAME,
       HDR.CNTC,
       DTL.EM_TYP,
       CASE 
           WHEN EM_TYP = 'TO' THEN dtl.EM_ADDRESS 
           WHEN EM_TYP = 'CC' THEN dtl.EM_ADDRESS 
           WHEN EM_TYP = 'BCC' THEN dtl.EM_ADDRESS 
       END AS [TO_RECIPIENTS]
FROM DEV_USR.dbo.T_WKLY_DIST_HDR hdr
JOIN DEV_USR.dbo.T_WKLY_RPT_DIST dtl
ON dtl.CUST_NUM = hdr.CUST_NUM
GROUP BY    
HDR.CUST_NUM,
       HDR.CUST_NAME,
       HDR.CNTC,
       DTL.EM_TYP,
       DTL.EM_ADDRESS
result for [To] would be something like = me@mycompany.com; you@mycompany.com ; us@mycompany.com - so that it emails one time properly
more ▼

asked Feb 11, 2011 at 04:29 PM in Default

siera_gld gravatar image

siera_gld
1k 80 84 85

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

This looks like a task for a three-fold application of the XML PATH hack to concatenate lists.

I'm not yet awake, otherwise I would have a go, but if you're in a hurry, [google "XML PATH STRING"][1], and you should find something helpful.

Do one sub-query for each of "To", "CC", "BCC", and that should sort you out.

[1]: http://www.google.co.uk/search?sourceid=chrome&ie=UTF-8&q=xml+path+string
more ▼

answered Feb 12, 2011 at 02:17 AM

ThomasRushton gravatar image

ThomasRushton ♦
34.2k 18 20 44

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x991
x946
x18

asked: Feb 11, 2011 at 04:29 PM

Seen: 1194 times

Last Updated: Feb 12, 2011 at 01:09 AM