SQL Select distinct from multiple fields returning only one row

Question

I have a table with the following columns in SQL Server:

MEMBERID, MEMBEREMAIL, FATHEREMAIL, MOTHEREMAIL, MEMBERNAME

MEMBERID is PK. The three email columns are not unique, so the same email may appear several times in the same row AND in several rows.

I am trying to extract a unique list of emails, and for each email also get a memberid and membername (it does not matter from which record).

For example if I have three rows:

1   [email protected]   [email protected]   [email protected]   Mark
2   [email protected]   [email protected]   [email protected]   John
3   [email protected]   [email protected]   [email protected]   Susan

I want to get the three emails ([email protected], [email protected], [email protected]) and for each of those a MEMBERID in which they appear. It does NOT which MEMBERID (for example for [email protected] I don't care if I get the values 1 and Mark or 2 and John or 3 and Susan, as long as [email protected] appears only once in the results.

If I use DISTINCT when trying to return the email and memberid and membername, of course I get all of the rows.

Andriy M · Accepted Answer · 2012-07-03 14:37:08Z

You could use a subquery to normalize all emails. Then you can use row_number to filter out one memberid, membername per email:

select  *
from    (
        select  row_number() over (partition by email order by memberid) as rn
        ,       *
        from    (
                select  MEMBERID
                ,       MEMBERNAME
                ,       MEMBEREMAIL as email
                from    YourTable
                union all
                select  MEMBERID
                ,       MEMBERNAME
                ,       FATHEREMAIL
                from    YourTable
                union all
                select  MEMBERID
                ,       MEMBERNAME
                ,       MOTHEREMAIL
                from    YourTable
                ) as emails
        ) num_emails
where   rn = 1

You could also normalize the emails using the UNPIVOT clause, like this:

select  *
from    (
        select  row_number() over (partition by email order by memberid) as rn
        ,       *
        from    (
                select  MEMBERID
                ,       MEMBERNAME
                ,       email
                from    YourTable
                unpivot (
                                email
                        for     emailOwner
                        in      (
                                MEMBEREMAIL,
                                FATHEREMAIL,
                                MOTHEREMAIL
                                )
                        ) as u
                ) as emails
        ) num_emails
where   rn = 1

Try both versions at SQL Fiddle:

Jester · Accepted Answer · 2012-07-03 09:55:55Z

0

This code will give you the right group of distinct emails: then you can create a cursor out of the query members and then get the comma seperated list of mails per memberid with this concept I would create an output table for this will be easyer if you need it for future use and would make a store procedure for this to create the custom table

select mem.*, mails.MEMBEREMAIL
from (
    select MEMBERID,max(MEMBERNAME) as MEMBERNAME
    from table
    group by MEMBERID
)  as mem 
inner join 
(
    select distinct MEMBERID, MEMBEREMAIL
    from (
        select MEMBERID, MEMBEREMAIL
        from table
        union
        select MEMBERID, FATHEREMAIL
        from table
        union
        select MEMBERID, MOTHEREMAIL
        from table
    ) as mail
) as mails on mem.MEMBERID = mails.MEMBERID

answered Jul 3, 2012 at 9:55

Jester

3,4215 gold badges32 silver badges47 bronze badges

1 Comment

user1480192 Over a year ago

This code does not generate distinct emails - running it on the sample data generates 8 rows with multiple repeats. John 1 [email protected] John 1 [email protected] Mark 2 [email protected] Mark 2 [email protected] Mark 2 [email protected] Susan 3 [email protected] Susan 3 [email protected] Susan 3 [email protected]

Collectives™ on Stack Overflow

SQL Select distinct from multiple fields returning only one row

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related