MSSQL - Multi-rows string concatenating

Multi-rows string concatenating.

I used these key words searching it from google.

SQL string Concatenate 

3 Ways

1. for xml path

2. COALESCE

3. STRING_AGG

for xml path, special character handling method.

Some chinese for xml path reference，如何透過for xml path 搭配STUFF將多ROW資料合併同一ROW and 食譜好菜 SQL Server 使用「FOR XML」語法做欄位合併

And performance comparison.

for xml path vs CTE

for xml path vs STRING_AGG

sql-server-v-next-string_agg-performance

sql-server-v-next-string_agg-performance

Conclusion, if you are using MSSQL 2017 later version, just use the STRING_AGG.

XML PATH method string concatenating

DECLARE @temp_origin TABLE
(
	ID INT
  , input_date DATETIME
  , name VARCHAR(50)
) 
-- a sample temp table
INSERT INTO @temp_origin
SELECT 1, '2021-05-05', 'Adam%'
 UNION
SELECT 1, '2021-05-05', 'Gray&'
 UNION
SELECT 1, '2021-05-05', 'Jacy'
 UNION
SELECT 2, '2021-05-08', 'Henry'
 UNION
SELECT 2, '2021-05-08', 'Alice'
 UNION
SELECT 3, '2021-05-15', 'Ben'



-- xml path method
SELECT DISTINCT 
	   temp.ID
	 , input_date
	 , inner_concatenate_name
  FROM @temp_origin AS temp
 OUTER APPLY (SELECT DISTINCT ID 
		   , STUFF((SELECT ',' + name
				  FROM @temp_origin AS inner_list
				 WHERE inner_list.ID = outer_list.ID 
				   for xml path(''), root('MyString'), type
				).value('/MyString[1]','varchar(max)')
			  ,1,1,'') AS inner_concatenate_name
		   FROM @temp_origin AS outer_list
		 ) AS T
 WHERE temp.ID = T.ID


/*
-- for someone who don't understand the xml path work, you can uncomment this block

SELECT DISTINCT ID 
	 , STUFF((SELECT ',' + name
				FROM @temp_origin AS inner_list
			   WHERE inner_list.ID = outer_list.ID 
			   for xml path(''), root('MyString'), type
			 ).value('/MyString[1]','varchar(max)')
			,1,1,'') AS inner_concatenate_name
  FROM @temp_origin AS outer_list

*/

NOTE: The part of is handling the special character like “&”

                         , root('MyString'), type
).value('/MyString[1]','varchar(max)')

If you don’t add, the “&” will become “&” in XML path

COALESCE method string concatenating

/*
-- COALESCE method seems cannot simply do it in one shot
DECLARE @val Varchar(MAX); 

SELECT @val = COALESCE(@val + ', ' + name, name) 
  FROM @temp_origin 
  
SELECT @val;
*/

STRING_AGG method string concatenating

-- STRING_AGG method
SELECT ID
	 , input_date
	 , STRING_AGG( ISNULL(name, ' '), ',') As name 
  From @temp_origin
 GROUP BY ID, input_date

Here’s the testing result