SQL Rows to Column

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
SELECT [rep_email] + ';' -- semi colon separator
FROM ###emails# E
WHERE E.company=#emails.company
) 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.

Cup size   
Select size then click on coffee cup.
This entry was posted in Code, Snippets, SQL and tagged , , . Bookmark the permalink.