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

avatar image

siera_gld
1k 82 88 93

(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", and you should find something helpful.

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

more ▼

answered Feb 12, 2011 at 02:17 AM

avatar image

ThomasRushton ♦♦
39.8k 20 49 52

(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:

x1146
x1066
x16

asked: Feb 11, 2011 at 04:29 PM

Seen: 1362 times

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

Copyright 2016 Redgate Software. Privacy Policy