組合多筆資料為一個字串

在 SQL Server 中如何將多筆資料列中屬於同一群組的字串組合起來 ?
在 stackoverflow 找到這一則討論 Optimal way to concatenate/aggregate strings ，文中有兩個解法分別是：

1. CTE (COMMON TABLE EXPRESSION)
2. XML PATH

解決方法

將其範例調整可以直接執行在暫存資料表中執行如下
CTE (COMMON TABLE EXPRESSION) 範例

create table #t (id int, name varchar(20));

insert into #t
values (1, 'Matt'), (1, 'Rocks'), (2, 'Stylus');

WITH Partitioned AS
(
    SELECT 
        ID,
        Name,
        ROW_NUMBER() OVER (PARTITION BY ID ORDER BY Name) AS NameNumber,
        COUNT(*) OVER (PARTITION BY ID) AS NameCount
    FROM #t
),
Concatenated AS
(
    SELECT ID, CAST(Name AS nvarchar) AS FullName, Name, NameNumber, NameCount FROM Partitioned WHERE NameNumber = 1

    UNION ALL

    SELECT 
        P.ID, CAST(C.FullName + ', ' + P.Name AS nvarchar), P.Name, P.NameNumber, P.NameCount
    FROM Partitioned AS P
        INNER JOIN Concatenated AS C ON P.ID = C.ID AND P.NameNumber = C.NameNumber + 1
)
SELECT 
    ID,
    FullName
FROM Concatenated
WHERE NameNumber = NameCount

drop table #t ;

XML PATH 範例

create table #t (id int, name varchar(20));

insert into #t
values (1, 'Matt'), (1, 'Rocks'), (2, 'Stylus');

select * from #t;


select  id
        ,Names = stuff((
    select ', ' + name as [text()]
    from #t xt
    where xt.id = t.id
    for xml path('')
   ), 1, 2, '')
from #t t
group by id;

drop table #t ;

比較

第一種 SQL CTE 實務上經常運用，反倒是第二種作法很陌生，在此也認識到了 STUFF 與 FOR XML 兩個方法。
就程式碼內容上第一段顯然就較難理解…
經過實際測試這兩段在大量資料量下執行的效能如何

筆數	SQL CTE	FOR XML PATH
100	0 sec	0 sec
1,000	3 sec	0 sec
10,000	超過 60 sec	0 sec

參考文章

[SQL]將多筆資料合併為一筆顯示(FOR XML PATH) - 點部落
Optimal way to concatenate/aggregate strings – stackoverflow