TSQL - String Concatenation

On current project, I ran into a scenario where I needed to concatenate a series of rows into a single string value.  Nothing major, just concatenate them with a delimiter and return the result.  As all of us Microsoft SQL developer's know and deal with is the lack of a SQL supported function that is able to assist us in this "minor" feature.

As this is my knowledge dump for techniques and other methods used for the various platforms, here we go:

Construct:

The first step is to identify what information you want, the basic's of query design.  

 SELECT Email  
 FROM Contacts   
 Group By Email   

Group by is conditional, depending on your situation and how you organize your referential integrity.

Stage

Next, lets build our environment, not the SProc just the supporting actors in the process.

 declare @email varchar(max) = ''  
   
 SELECT @email += Email + ', '  
 FROM Contacts   
 Group By Email   

This simply concatenates each of the string value rows into a single variable.  This allows us to bring all the information into a single source variable, that contains everything we need.

Unfortunately, this also introduces a problem with the output.  The value in the variable will end with a comma (,) which in most cases is not problematic; except for those OCD candidates that laser focus on that one character at the end of the string.  I will solve this in the Delivery section.

Delivery

Last and final stage to finalize the result of the concatenation:

 declare @email varchar(max) = ''  
   
 SELECT @email += Email + ', '  
 FROM Contacts   
 Group By Email   
   
 select Substring(@email, 0, len(@email))  

Since TSQL (MS-SQL) is a Zero-Based index platform, using Substring with a upper limit of the length of the string (@email), it will return all the character's up to the index marker location right before the comma.  As SQL looks at the location marker before the character position, versus some other languages that look at the location marker after the character.