How to use only SQL to Concatenate Strings in Aggregation
USE CombinedArchaeologyDB
GO
SELECT S.SquareDesignation, --This is the element that we will group by
STUFF( --Stuff is a way of removing the first ', ' from the list
(SELECT ', ' + CAST(L.LocusIdentity AS VARCHAR(MAX)) --This selects a ', ' concatenated with the locus identity
FROM ARCH_Locus AS L --From the appropriate table
WHERE L.SquareID=S.SquareID --This makes this a correlated subquery so that we only get the things we want
FOR XML PATH(''),TYPE --Specifies that we want XML with NO SURROUNDING ROW ELEMENT {PATH('')} and that we want it as XML type
).value('(./text())[1]','VARCHAR(MAX)'),1,2,'') AS Loci --The .value specifies a xquery of (./text())[1] resulting in type VARCHAR(MAX). The 1,2,'' Is part of STUFF that removes the first two characters.
FROM ARCH_Square as S --From the outer table
GROUP BY S.SquareID, S.SquareDesignation --Grouped by...