How to rewrite this query to avoid duplicates , please

Hi Milano,



Regarding your description, in your scenario, every account can have 3 or at least one valid email. You'd like to retrive an email list in which only one among the email addresses that belong to the same AccountID can show up for each AccountID, right?



If my assumption is right, please see the below code.

SELECT AccountID,COALESCE(EmailWork,EmailHome,EmailOther) AS Email FROM #MyPhoneList --you may exchange the columns order if some email has a priority
--just in case you need the Sourceflag column, please see below.
SELECT AccountID,COALESCE(EmailWork,EmailHome,EmailOther) AS Email,
CASE WHEN EmailWork IS NOT NULL AND IsValidEmailWork=1 AND IsOffersToWorkEmail=1 THEN 'work,' ELSE '' END 
+CASE WHEN EmailHome IS NOT NULL AND IsValidEmailHome=1 AND IsOffersToHomeEmail=1 THEN 'home,' ELSE '' END
+CASE WHEN EmailOther IS NOT NULL AND IsOffersToOtherEmail=1 AND IsValidEmailOther=1 THEN 'other' ELSE '' END AS Sourceflag
FROM #MyPhoneList

If you have any question, feel free to let me know.



Best regards,

Eric Zhang