How to rewrite this query to avoid duplicates , please

As per your table structure, I feel what you need is this

  SELECT AccountID 
     , MAX(CASE WHEN IsOffersToWorkEmail    = 1 
  AND  EMAILWORK      IS NOT NULL 
  AND  IsValidEmailWork    = 1 THEN EmailWork END)     AS WorkEmail,
MAX(CASE WHEN IIsOffersToHomeEmail    = 1 
  AND  EMAILHOME      IS NOT NULL 
  AND  IsValidEmailHome    = 1 THEN EmailHome END)     AS HomeEmail,
MAX(CASE WHEN IIsOffersToOtherEmail    = 1 
  AND  EMAILOther      IS NOT NULL 
  AND  IsValidEmailOther    = 1 THEN EmailOther END)     AS OtherEmail
  FROM #MyPhoneList (NoLock) eml
  WHERE (
  (IsOffersToWorkEmail    = 1 
  AND  EMAILWORK      IS NOT NULL 
  AND  IsValidEmailWork    = 1)
  OR (IsOffersToHomeEmail    = 1 
  AND  EMAILHOME      IS NOT NULL 
  AND  IsValidEmailHome    = 1)
  OR (EmailOther      = 1 
  AND  EMAILOther      IS NOT NULL 
  AND  IsValidEmailOther    = 1)
  GROUP BY AccountID
 

---

Please Mark This As Answer if it solved your issue

Please Vote This As Helpful if it helps to solve your issue

Visakh

----------------------------

My Wiki User Page

My MSDN Page

My Personal Blog

My Facebook Page