Concatenate Values in a Column in a Child Table so they Appear as one Value when Joined to the Parent Table

Here is some code that might make your SQL Server reporting queries a bit simpler. The solution given here allows this task to be completed uning a join rather than involving variables and function calls.

First I'll set up some sample data

SET NOCOUNT ON

CREATE TABLE Family
(
    FamilyID INT NOT NULL IDENTITY(1, 1) PRIMARY KEY,
    FamilyName VARCHAR(20) NOT NULL
)

CREATE TABLE FamilyMember
(
    MemberID INT NOT NULL IDENTITY(1, 1) PRIMARY KEY,
    FamilyID INT NOT NULL REFERENCES Family (FamilyID),
    MemberName VARCHAR(20) NOT NULL
)

DECLARE @LastID INT

INSERT INTO Family(FamilyName) VALUES ('Brunger')
SET @LastID = @@IDENTITY
INSERT INTO FamilyMember(FamilyID, MemberName) VALUES (@LastID, 'David')
INSERT INTO FamilyMember(FamilyID, MemberName) VALUES (@LastID, 'Katherine')
INSERT INTO FamilyMember(FamilyID, MemberName) VALUES (@LastID, 'Jack')

INSERT INTO Family(FamilyName) VALUES ('Bailey')
SET @LastID = @@IDENTITY
INSERT INTO FamilyMember(FamilyID, MemberName) VALUES (@LastID, 'Elizabeth')
INSERT INTO FamilyMember(FamilyID, MemberName) VALUES (@LastID, 'Chris')
INSERT INTO FamilyMember(FamilyID, MemberName) VALUES (@LastID, 'Lucy')
INSERT INTO FamilyMember(FamilyID, MemberName) VALUES (@LastID, 'James')
INSERT INTO FamilyMember(FamilyID, MemberName) VALUES (@LastID, 'Matty')

SELECT * FROM Family
SELECT * FROM FamilyMember

Now comes the clever query using the FOR XML clause to do the concatenation for us, but ensuring that no XML tags are inserted into the resultant string. The CROSS APPLY clause calls the enclosed "function" for each row in the result set.

SELECT
    FamilyName,
    -- 1 is subtracted from the length of Members rather
    -- than 2 as the LEN() function ignores the training
    -- whitespace
    LEFT(Members, LEN(Members) - 1) AS Members
FROM
    Family CROSS APPLY
    (
        SELECT
            -- No column alias such as [text()] or "*"
            -- is necessary here as the concatenation
            -- operation means that the the column is
            -- already nameless
            MemberName + ', '
        FROM
            FamilyMember
        WHERE
            FamilyMember.FamilyID = Family.FamilyID
        -- The empty string here means that now 
        -- tag is generated for each row
        FOR XML PATH('')
    ) AS Data(Members)

Now tidy up.

DROP TABLE FamilyMember
DROP TABLE Family

This gives the following output:

FamilyID    FamilyName
----------- --------------------
1           Brunger
2           Bailey

MemberID    FamilyID    MemberName
----------- ----------- --------------------
1           1           David
2           1           Katherine
3           1           Jack
4           2           Elizabeth
5           2           Chris
6           2           Lucy
7           2           James
8           2           Matty

FamilyName           Members
-------------------- --------------------------------------
Brunger              David, Katherine, Jack
Bailey               Elizabeth, Chris, Lucy, James, Matty