[SQL SERVER][TSQL]多筆資料變單筆資料
最近有個需求，須把相同群組的多筆資料給合成一筆資料(用 , 區隔)
查了一下MSDN大概有2種做法，自己記錄一下。
原本資料
1. stuff搭配xml path
select STUFF ( (select ','+c2
from
(
select 'A' as 'c1','url1' as 'c2'
union
select 'A' as 'c1','url2' as 'c2'
union
select 'A' as 'c1','url3' as 'c2'
) t1
FOR XML PATH('') ) ,1,1,'' ) as 'url'
2. COALESCE
declare @mydwl varchar(8000)
select @mydwl=COALESCE(@mydwl + ',', '') + c2
from
(
select 'A' as 'c1','url1' as 'c2'
union
select 'A' as 'c1','url2' as 'c2'
union
select 'A' as 'c1','url3' as 'c2'
) a
SELECT @mydwl
參考