Using SQL Server STUFF function to turn rows in a column

Example below:

CREATE TABLE #TempMaster (
[ID] int,
[Name] nvarchar(50)
)

CREATE TABLE #TempChild (
MasterID int,
[ID] int,
[Name] nvarchar(50)
)

INSERT INTO #TempMaster ([ID], [Name]) VALUES (1, ‘Master 1’)
INSERT INTO #TempMaster ([ID], [Name]) VALUES (2, ‘Master 2’)
INSERT INTO #TempMaster ([ID], [Name]) VALUES (3, ‘Master 3’)
INSERT INTO #TempMaster ([ID], [Name]) VALUES (4, ‘Master 4’)
INSERT INTO #TempMaster ([ID], [Name]) VALUES (5, ‘Master 5’)

INSERT INTO #TempChild (MasterID, [ID], [Name]) VALUES (1, 1, ‘Child 1’)
INSERT INTO #TempChild (MasterID, [ID], [Name]) VALUES (2, 2, ‘Child 2’)
INSERT INTO #TempChild (MasterID, [ID], [Name]) VALUES (2, 3, ‘Child 3’)
INSERT INTO #TempChild (MasterID, [ID], [Name]) VALUES (3, 4, ‘Child 4’)
INSERT INTO #TempChild (MasterID, [ID], [Name]) VALUES (3, 5, ‘Child 5’)
INSERT INTO #TempChild (MasterID, [ID], [Name]) VALUES (3, 6, ‘Child 6’)
INSERT INTO #TempChild (MasterID, [ID], [Name]) VALUES (5, 7, ‘Child 7’)
INSERT INTO #TempChild (MasterID, [ID], [Name]) VALUES (5, 8, ‘Child 8’)
INSERT INTO #TempChild (MasterID, [ID], [Name]) VALUES (5, 9, ‘Child 9’)
INSERT INTO #TempChild (MasterID, [ID], [Name]) VALUES (5, 10, ‘Child 10’)

SELECT [TM].ID AS MasterID, TM.[Name] AS MasterName, TC.Names AS ChildNames
FROM #TempMaster AS TM
LEFT JOIN
(SELECT TC.MasterID
, STUFF((
SELECT ‘, ‘ + T.[Name]
FROM #TempChild AS T WITH (NOLOCK)
WHERE (TC.MasterID = T.MasterID)
FOR XML PATH, TYPE).value(N’.[1]’, N’nvarchar(max)’ –stops HTML characters such as &, < or > being converted to &, < and > from the XML function
), 1, 2, ”) AS Names
FROM #TempChild AS TC WITH (NOLOCK)
GROUP BY TC.MasterID
) AS TC ON (TM.ID = TC.MasterID)