1

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: enter image description here

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

enter image description here

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

Dale K
  • 25,246
  • 15
  • 42
  • 71
ValiantSirDK
  • 75
  • 5
  • the expected results remove data, for example: in the example data, you have BusinessA Admin Admin Account, then Samantha Gray Receptionist and both have the admin email address, what is your logic for eliminating the data about Samantha Gray and subsequent records which share the admin email address? Also, you should add an attempt you have made at writing the query to your post. – Ryan Wilson Jul 28 '19 at 02:15
  • We're creating a simple CRM using Exchange, however Exchange needs a unique email address. If we just pull in the first email, we won't capture all the email grouping required, so we're trying to join the groupings before importing into Exchange. – ValiantSirDK Jul 28 '19 at 02:21
  • I've tried following: https://stackoverflow.com/questions/15154644/group-by-to-combine-concat-a-column?noredirect=1&lq=1 and https://stackoverflow.com/questions/451415/simulating-group-concat-mysql-function-in-microsoft-sql-server-2005 and various others on Stack Overflow and other pages. Most seem to reference multiple tables, but all my data is in a single table, so it's a little hard to convert. – ValiantSirDK Jul 28 '19 at 02:23

2 Answers2

2

I think you can just use aggregation:

select businessid, businessname, max(firstname),
       max(lastname), max(position),
       email,
       max(emergencycontact),
       max(accountscor),
       max(whcontact)
from t
group by businessid, businessname, email;
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
2

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).

cte6
  • 637
  • 4
  • 8