SQL Assistance - STUFF and XML Path concatenating data

Question

I know this question has been asked a lot, but I can't seem to get it to work with my data. I've tried following the help in a few articles, but rather than filtering my data, it just shows everything. I'm trying to pull together 3 tables and group on email address. My 3 tables are:

I'm trying to compile the data so I get an output like this:

I've been able to get all the data into a single line for each email (thanks to the support I got here: SQL Assistance - Grouping other fields by common email), but when I try to do a STUFF on the CompanyID and then try to bring in the services for all the companies, it just brings back all data instead of the filtered data. I can get the stuff to work for the 1st companyID, but not if an email has two companyIDs associated.

Can anyone assist? Sorry about the explanation, I'm quite confused at the moment. Thanks Luke

Hmm, seems like there's a flaw with your design. The contqft table should be split in 2; one with the contact details and another for the many to many relationship to company. — Thom A, Jul 28 '19 at 08:27
Also, please don't use images to display your data; we can't use it. Please provide it as tabular formated `text`, or as DML and DDL statements. — Thom A, Jul 28 '19 at 09:56

score 0 · Answer 1 · answered Jul 28 '19 at 09:04

Please check if this can help you...

;WITH CTEContacts AS
    (   SELECT DISTINCT ContactEmail,
                ContactActive 
        FROM    Contacts  
    )
    SELECT  ContactEmail,
            ContactActive,
            ISNULL(STUFF((SELECT DISTINCT
            ', ' + CAST(Contacts.CompanyId AS NVARCHAR(3))
            FROM Contacts
            WHERE Contacts.ContactEmail = CTEContacts.ContactEmail 
            FOR xml PATH ('')), 1, 2, ''), '') AS CompanyId, 
            ISNULL(STUFF((SELECT DISTINCT
            ', ' + CAST([Services].ServiceName AS NVARCHAR(50))
            FROM [Services]
            JOIN Companies
            ON  [Services].CompanyId = Companies.CompanyId
            JOIN Contacts
            ON   Contacts.ContactEmail = CTEContacts.ContactEmail
            AND  Contacts.CompanyId = Companies.CompanyId
            WHERE Contacts.ContactEmail = CTEContacts.ContactEmail 
            FOR xml PATH ('')), 1, 2, ''), '') AS ServiceName
    FROM    CTEContacts
    ORDER BY CTEContacts.ContactEmail

score 0 · Answer 2 · answered Jul 28 '19 at 10:10

Here's another possible answer, this one without using a common table expression:

select  ContactEmail,
    --Note: Current schema allows duplicate email addresses to be active and inactive...
    ContactActive = max(ContactActive),
    CompanyID = stuff((
        select  ',' + cast(C2.CompanyID as nvarchar(10))
        from    Contact C2
        where   C2.ContactEmail = C1.ContactEmail
        group by C2.CompanyID
        order by C2.CompanyID
        for xml path('')), 1, 1, ''),
    Services = stuff((
        select  ',' + space(1) + ServiceName
        from    Contact C2
        -- Note: This join isn't supported by a single Foreign Key contstraint
        join    [Service] S on S.CompanyID = C2.CompanyID
        where   C2.ContactEmail = C1.ContactEmail
        group by S.CompanyID, ServiceName
        order by S.CompanyID, ServiceName
        for xml path('')), 1, 2, '')
from Contact C1
group by ContactEmail
order by CompanyID, min(ContactID)

And for anyone else who wants a crack at this question, here's the setup code I used inferred from the example table data...

if object_id('Contact') is not null drop table Contact;
if object_id('Service') is not null drop table [Service];
if object_id('Company') is not null drop table Company;
go
create table Company (
    CompanyID int not null,
    CompanyName nvarchar(50) not null,
    constraint PK_Company primary key (CompanyID),
    constraint ixU_Company_CompanyName unique (CompanyName)
);
insert Company (CompanyID, CompanyName)
values  (1, 'CompanyA'),
    (2, 'CompanyB'),
    (3, 'CompanyC'),
    (4, 'CompanyD');
go
create table [Service] (
    ServiceID int not null,
    ServiceName nvarchar(50) not null,
    CompanyID int not null,
    constraint PK_Service primary key (ServiceID),
    constraint ixU_Service_ServiceName unique (ServiceName),
    constraint FK_Service_CompanyID_Company_CompanyID
        foreign key (CompanyID)
        references Company (CompanyID)
);
insert [Service] (ServiceID, ServiceName, CompanyID)
values  (1, 'Electricity', 1),
    (2, 'Water', 1),
    (3, 'Gas', 2),
    (4, 'Lighting', 2),
    (5, 'Air Con', 4),
    (6, 'Sanitation', 4);
go
create table Contact (
    ContactID int not null,
    ContactEmail nvarchar(50) not null,
    ContactActive nchar(1) not null
        constraint DF_Contact_ContactActive default ('Y'),
    CompanyID int not null,
    constraint PK_Contact primary key (ContactID),
    constraint CK_Contact_ContactActive check (ContactActive in ('N', 'Y')),
    constraint FK_Contact_CompanyID_Company_CompanyID
        foreign key (CompanyID)
        references Company (CompanyID)
);
insert Contact (ContactID, ContactEmail, ContactActive, CompanyID)
values  (1, 'admin@admin.com', 'Y', 1),
    (2, 'admin@admin.com', 'Y', 1),
    (3, 'admin1@2.com', 'Y', 2),
    (4, 'admin2@2.com', 'Y', 2),
    (5, 'admin@3.com', 'Y', 3),
    (6, 'admin@4.com', 'Y', 4),
    (7, 'accounts@4.com', 'Y', 4),
    (8, 'enquiries@4.com', 'Y', 4),
    (9, 'services@4.com', 'N', 4),
    (10, 'admin@admin.com', 'Y', 4);
go

SQL Assistance - STUFF and XML Path concatenating data

2 Answers2