Hi,

I have a question...and I'm a little rusty on my SQL.

I have a database in MS-SQL Express 2005 which have several email fields as columns...

ex: ID, UserName, Email, Contact1Email, Contact2Email

I want to do a query that will give the results as...

ID, UserName, EmailField

Where EmailField will be a separate record for each of the email columns above.

Ex: Record 1 - ID, UserName, EmailField (contains Email)
Record 2 - ID, UserName, EmailField (Contains Contact1Email)
Record 3 - ID, UserName, EmailField (Contains Contact2Email)
Record 4 - ID (Next Record), UserName, EmailField (Contains Email)

and so on.

Can anyone help?

It would be great to do it killing duplicate emails.

Thanks
Donna Z

Write a bunch of SELECT queries and pull them together with the UNION operator.

Something like:

selectdistinct*from(
select id, username, email from table
union
select id, username, contact1email from table
union
select id, username, contact2email from table
)as emails

This will not remove people who have the same email under different names though.

This will not remove people who have the same email under different names though.

Then use a "group by" clause on your "select distinct" query, and drop the distinct...

I've just added an "origin" column, if you need to distinguish where each row come from

select id, username, email, origin from(
select id, username, email, 'table1' from table
union
select id, username, contact1email, 'table2' from table
union
select id, username, contact2email, 'table3' from table
)as emails
group by emails.email

Tripy is right, and notice how he gave the inner union query a correlation name. It won't run otherwise. Also, everybody should note that a union query automatically runs an implicit distinct. To union without an implied distinct, use the union all command.

Everyone - thanks.