Grouping other fields by common email

I’d appreciate any assistance people could provide with this one. I’ve tried to follow a couple of other posts (GROUP BY to combine/concat a column), but when I do this, it combines everything.

I have a table in SQL that holds contact data. Some contacts in the database have the same email address, particularly admin@, accounts@, etc. We also have a list of contact preferences for each contact: Emergency Contact, Accounts, WHS, etc. I’m trying to select the results of the table, but only one row per email address.

My data looks like this:

However, I’d like it to group the rows together that have the same email address (I don’t care about the names). I’d also like it to look at the contact preference fields and if even one of the joined fields had a yes, show a Y, otherwise show a N – see below example

As I mentioned, all my tests have been unsuccessful I’m afraid, so I would appreciate any assistance that you can provide.

Answer

You can use the following:

;WITH selected as (
select min(ContactID) as ContactID, Mail, max(EmergencyContact) as EmergencyContact, 
max(AccountsCon) as AccountsCon, max(WHSContact) as WHSContact
from t
GROUP BY Mail
)
select t.ContactID, t.BusinessID, t.BusinessName, t. FirstName, t.LastName, t.Position, t.Mail, s.EmergencyContact, s.AccountsCon, s.WHSContact
from selected as s
inner join t as t ON t.ContactID = s.ContactID

This way you get the contactid, businessid, businessname, firstname, lastname and position from the first record found with each email and the last 3 columns you get using max (taking advantage that Y is greater than N, so if there is at least one Y it will get Y).