I had an interesting challenge this week where I needed to turn rows of email addresses into a crosstab or a single column so I could use it to send out emails. I had to do this at a grouped level for different companies and there may be from 1-5 different email addresses. Here is the SQL solution.
SELECT DISTINCT company, C.rep_email
FROM #emails
CROSS APPLY
(
SELECT [rep_email] + ';' -- semi colon separator
FROM ###emails# E
WHERE E.company=#emails.company
FOR XML PATH('')
) C(rep_email)
Then I use coldfusion to create a mail link but you can use any tool that allows you to format the mail to parameter with the new rep_email values.
I looped through each company and created a mail link for my app, but could have sent out all the emails at once if I had desired.
