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

{{{#!highlight sql
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 the 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...
}}}