How to use only SQL/XQuery to Concatenate Strings in Aggregation
Sometimes you want to compile a list of things from a set of rows in SQL. SQL Server does not have a native concate aggregation operator. You can either write your own using some CLR language like C# and then go through the pain of adding the resultant .dll file to your DB and turning on this type of functionality or you can use the following as an example. This example is taken from the world of Archaeology.
1 USE CombinedArchaeologyDB
2
3 GO
4
5 SELECT S.SquareDesignation, --This is the element that we will group by
6 STUFF( --Stuff is a way of removing the first ', ' from the list
7 (SELECT ', ' + CAST(L.LocusIdentity AS VARCHAR(MAX)) --This selects a ', ' concatenated with the locus identity
8 FROM ARCH_Locus AS L --From the appropriate table
9 WHERE L.SquareID=S.SquareID --This makes this a correlated subquery so that we only get the things we want
10 FOR XML PATH(''),TYPE --Specifies that we want XML with NO SURROUNDING ROW ELEMENT {PATH('')} and that we want it as XML type
11 ).value('(./text())[1]','VARCHAR(MAX)'),1,2,'') AS Loci --The .value specifies a xquery of (./text())[1] resulting in type VARCHAR(MAX).
12 --The 1,2,'' is the part of STUFF that removes the first two characters.
13 FROM ARCH_Square as S --From the outer table
14 GROUP BY S.SquareID, S.SquareDesignation --Grouped by...
15