T-SQL combine indirectly related tables into single query-CodePudding

I have a parent table called "Businesses" with two different related/child tables (Affiliates and Employees) related to a business but not directly to each other.

I want a single SQL query that will show all businesses, affiliates and employees with one line item for each employee. For example, given business "B1" with 2 Affiliates (A1 and A2) and two Employees (E1 and E2), I want a query with results that look something like:

Business	Affiliates	Empl #	Empl Name
B1	A1, A2	1	John
B1	A1, A2	2	Bob

The closest I've gotten is output that looks like:

Business	Affiliates	Empl #	Empl Name
B1	A1	1	John
B1	A1	2	Bob
B1	A2	1	John
B1	A2	2	Bob

But as you can see, each employee shows twice (once for each affiliate).

Any suggestions on how to achieve the desired result?

CodePudding user response：

With SQL Sever 2017 and later:

WITH existing as (

   -- your existing query goes here

) 
SELECT Business, string_agg(Affiliates, ',') as Affiliates,
     [Empl #], MAX([Empl Name]) As [Empl Name]
FROM existing
GROUP BY Business, [Empl #]

CodePudding user response：

You could do this.

DECLARE @Sample TABLE ( business char(12), affiliates VARCHAR(25), empid int);

INSERT INTO @Sample VALUES
    ( 'B1', 'A1', 50 ),
    ( 'B1', 'A1', 80 ),
    ( 'B1', 'A2', 90 ),
    ( 'B1', 'A2', 50 );
    
SELECT  O.business,
        STUFF( (SELECT DISTINCT ', '   CAST(affiliates AS VARCHAR(25)) 
                FROM @Sample I WHERE I.business = O.business FOR XML PATH('')
                ), 1, 2, '')
FROM    @Sample O
GROUP BY O.business;

Result:

Change to suit your needs.