MSSQL - 多筆 row 字串連接

多筆 row 字串連接。

我是用下面的關鍵字去 google 的

SQL string Concatenate 

3種方法

1. for xml path

2. COALESCE

3. STRING_AGG

用 for xml path 遇到特殊字元的處理方式。

中文的 for xml path 的參考，如何透過for xml path 搭配STUFF將多ROW資料合併同一ROW， 以及食譜好菜 SQL Server 使用「FOR XML」語法做欄位合併。

還有 performance 的比較

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

結論，MSSQL 2017 之後版本的人就乖乖用 STRING_AGG 吧..

XML PATH 方法連接 strings

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


/*
-- 如果不明白 xml path 怎麼運作的，可以把這塊註解拿掉看一下OUTPUT

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: 這塊是在處理 “&” 之類的特殊字元。

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

如果你沒加這塊，"&" 在 XML path 轉換過後會變成 “&”

COALESCE 方法連接 strings

/*
-- COALESCE 方法沒辦法一個 SELECT 搞定，所以就不用了
DECLARE @val Varchar(MAX); 

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

STRING_AGG 方法連接 strings

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

測試結果