SQL: Multiple Columns Into One Column

I was recently asked to create a CSV file of client emails listed in a single column. The purpose was to make sure that when clients emailed the company, they wouldn't be seen as spam. The third party spam detector required it to be in this format. In a SQL table, a client record could contain multiple emails; personal, work, etc. The client table I was looking at had 10 columns dedicated to emails! Obviously there were a lot of null values that I would have to account for when I did this. 

Let's first create our demonstration table:

CREATE TABLE [dbo].[ClientEmail](
       [Id] [int] IDENTITY(1,1) NOT NULL,
       [CONTACT_EMAIL_1] [nvarchar](50) NULL,
       [CONTACT_EMAIL_2] [nvarchar](50) NULL,
       [CONTACT_EMAIL_3] [nvarchar](50) NULL,
       [CONTACT_EMAIL_4] [nvarchar](50) NULL

The ClientMail table should look something like this:


Here's the code I used:

select value as 'email'
into #tmpTable
from ClientEmail
cross apply
) c(col, value)
where value is not  null
order by id, col

The reason why I did it this way instead of using UNION was to minimize the number of full table scans that occur (1 vs. 4). And if you had 5 columns or 8 columns that increases the number of scans to that number, and also makes a query using UNIONs much more cumbersome to write and look at. Here is the outcome that is created when the code is run:

In this particular scenario there is a chance of duplicate emails. For instance a spouse could use the same home email. It would be advisable to use DISTINCT when doing this.

Author: Christian Apostolu
Christian Apostolu's picture

Owner/founder of Crush The Soul, software developer, amateur athlete, frustrated musician. 

"Once we accept our limits, we go beyond them." - Albert Einstein