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