Thursday, 21 May 2009

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

No comments:

Post a comment