CONCAT different Rows into a single attribute value

Problem:

Using T-SQL, you need to concatenate values from different rows into a single attribute value.  The ideal solution would a function that allows you to CONCAT the values from a GROUP BY, much like a SUM(0) function might perform.

Solution

Let's say we want a single row, that lists out all the distinct Titles in the person.person table in the AdventureWorks2012 database. All we need is a string SUM() or CONCAT() command for a group.  We could imagine the command might look something like this:

       
with t as 
( select Title = isnull(p.Title,'n/a'), count(*) as TitleCnt
 from [Person].[Person] as p 
 group by p.Title
) 
Select AllTitles = MYROWCONCATFUNCTION(t.Title), sum(TitleCnt) as TitleCount from t
 
 

SQL Server 2017?
If you are using SQL Serer 2017 or Azure SQL Database, you can use the STRING_AGG function.

Not using the latest version?
For now, the most straight forward approach is to use FOR XML PATH. One key downside might be that for some, this command is considered a blackbox that might not be reliable in the longer term. So, if you are not comfortable using FOR XML PATH, go to the ProjectMX write-up on all the ways you can approach row concatenation using T-SQL:  ProjectMX: Concatenating row values in Transact-SQL.

FOR XML PATH

For a simple approach to concatenate the values from multiple rows is to setup a SubQuery using FOR XML PATH .

       
with t as 
( select Title = isnull(p.Title,'n/a'), count(*) as TitleCnt
 from [Person].[Person] as p 
 group by p.Title
) 
Select  
 sum(TitleCnt) as TitleCount
,(SELECT ',' + t.title + ' ' FROM t where (t.Title = t.Title) FOR XML PATH ('')) AS AllTitles
 from t;
 
 

With the output looking like this:

         TitleCount AllTitles
         ----------          ---------
         19972 ,Sr. ,Mrs. ,n/a ,Sra. ,Ms. ,Ms ,Mr.

To remove the leading comma, we can add a SUBSTRING function:

       
with t as 
( select Title = isnull(p.Title,'n/a'), count(*) as TitleCnt
 from [Person].[Person] as p 
 group by p.Title
) 
Select  sum(TitleCnt) as TitleCount
  ,SUBSTRING((SELECT  ',' + t.title + ' ' from t where (t.Title = t.Title)
  FOR XML PATH ('')),2,9999) AS AllTitles
  from t;
 
 

With the output looking like this:

         TitleCount AllTitles
         ----------          ---------
         19972 Sr. ,Mrs. ,n/a ,Sra. ,Ms. ,Ms ,Mr.


Need more information on FOR XML? See: m FOR XML (docs.microsoft.com)