2

I'm joining a number of tables in SQL Server and I need to return all possible manager's email address for a Facility. Possible manager(s) are determined by the country the facility is in.

Ideally I would like to only return a single row for a each facility and have all the managers email addresses in a single cell separated with a semicolon.

I have tried Pivot and COALESCE but I haven't been able to produce the desired result, does anyone know how to does this? Or am I going about this in completely the wrong way?

Tables 

CREATE TABLE Facility(  
    [FacilityId] [int] NOT NULL,
    [Address] [varchar](256)
    [CountryCode] [char](2) NOT NULL,
)

CREATE TABLE ManagementDivision(    
    [ManagementDivisionId] [int] NOT NULL,
    [Name] [varchar](256)
    [CountryCode] [char](2) NOT NULL,
)

CREATE TABLE Manager(
    [ManagerID] [numeric](8, 0) NULL,
    [ManagementDivisionId] [int] NOT NULL
)

CREATE TABLE Employee(
    [EmployeeId] [numeric](8, 0) NOT NULL,
    [mail] [varchar](1000) NULL
)

Example Data 

insert into Facility (FacilityId, Address, CountryCode) values(1, '123 Fake St' 'US')
insert into Facility (FacilityId, Address, CountryCode) values(2, '789 Forgery Rd' 'US')
insert into Facility (FacilityId, Address, CountryCode) values(3, '567 Bogus Bld' 'AU')

insert into ManagementDivision (ManagementDivisionId, Name, CountryCode) values(1, 'East Coast USA' 'US')
insert into ManagementDivision (ManagementDivisionId, Name, CountryCode) values(2, 'West Coast USA' 'US')
insert into ManagementDivision (ManagementDivisionId, Name, CountryCode) values(3, 'Central USA' 'US')
insert into ManagementDivision (ManagementDivisionId, Name, CountryCode) values(4, 'Australia' 'AU')

insert into Manager (ManagerId, ManagementDivisionId) values(1, 1)
insert into Manager (ManagerId, ManagementDivisionId) values(1, 2)
insert into Manager (ManagerId, ManagementDivisionId) values(2, 3)
insert into Manager (ManagerId, ManagementDivisionId) values(3, 4)

insert into Employee (EmployeeId, mail) values(1, 'manager1@somedomain.com')
insert into Employee (EmployeeId, mail) values(2, 'manager2@somedomain.com')
insert into Employee (EmployeeId, mail) values(3, 'manager3@somedomain.com')

Desired Result

FacilityId  Address         CountryCode ManagerEmail
1           123 Fake St     US          manager1@somedomain.com; manager2@somedomain.com;
2           789 Forgery Rd  US          manager1@somedomain.com; manager2@somedomain.com;
3           567 Bogus Bld   AU          manager3@somedomain.com;
TheLukeMcCarthy
  • 2,253
  • 2
  • 25
  • 34
  • Sql Server doesn't have cells, it has rows and columns. Using the correct terminology helps people finding your question via search engines and reduces duplicate questions. – StrayCatDBA Apr 19 '13 at 13:37

2 Answers2

1

This operation is called group_concat in MySQL. Here's a SO item on how to do this in SQL Server.

Simulating group_concat MySQL function in Microsoft SQL Server 2005?

Community
  • 1
  • 1
O. Jones
  • 103,626
  • 17
  • 118
  • 172
1

You can use Stuff

select 
F.FacilityId,
F.[Address],
F.CountryCode,
STUFF(
(SELECT Distinct ', '+E.mail
from Facility F1
inner join ManagementDivision MD on F1.CountryCode=MD.CountryCode
inner join Manager M on MD.ManagementDivisionId=M.ManagementDivisionId
INNER JOIN Employee E ON M.ManagerID=E.EmployeeId
WHERE F1.Address=F.Address
AND F1.CountryCode=F.CountryCode
AND F1.FacilityId=F.FacilityId
FOR XML PATH (''))
  ,1,2,'') AS Email
from Facility F

enter image description here

SQL Fiddle

Prahalad Gaggar
  • 11,389
  • 16
  • 53
  • 71