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...