SQL Server 2017

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

google
SQL string Concatenate

3種方法
1. xml path
2. COALESCE
3. STRING_AGG
https://www.mytecbits.com/microsoft/sql-server/concatenate-multiple-rows-into-single-string

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

https://blogs.lobsterpot.com.au/2010/04/15/handling-special-characters-with-for-xml-path/

中文的xml path的參考
https://coolmandiary.blogspot.com/2020/11/t-sql12for-xml-path-stuffrowrow.html
https://dotblogs.com.tw/supershowwei/2016/01/26/145353

還有 performance 的比較

xml path vs CTE
https://blog.darkthread.net/blog/col-merge-benchmark

xml path vs STRING_AGG
https://sqlperformance.com/2016/12/sql-performance/sql-server-v-next-string_agg-performance
https://sqlperformance.com/2017/01/sql-performance/sql-server-v-next-string_agg-performance-part-2

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

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
-- 如果不明白 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: the part of is handleing the special character like  "&"
--                                 , root('MyString'), type
--	     ).value('/MyString[1]','varchar(max)')
--
--      if you don't add, the "&" will become "&amp;"
*/

/*
-- 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
SELECT ID
     , input_date
     , STRING_AGG( ISNULL(name, ' '), ',') As name 
  From @temp_origin
 GROUP BY ID, input_date

ID	input_date	inner_concatenate_name
1	2021-05-05 00:00:00.000	Adam%,Gray&,Jacy
2	2021-05-08 00:00:00.000	Alice,Henry
3	2021-05-15 00:00:00.000	Ben

ID	input_date	name
1	2021-05-05 00:00:00.000	Adam%,Gray&,Jacy
2	2021-05-08 00:00:00.000	Alice,Henry
3	2021-05-15 00:00:00.000	Ben