有tableA如下数据
infoid txt
1 txt1
1 txt2
1 txt3
2 txt2
2 txt3合并成如下数据infoid txt
1 txt1|txt2|txt3
2 txt2|txt3求这条SQL语句
infoid txt
1 txt1
1 txt2
1 txt3
2 txt2
2 txt3合并成如下数据infoid txt
1 txt1|txt2|txt3
2 txt2|txt3求这条SQL语句
from tableA t group by t.infoid
as
(
select 1, 'txt1' union all
select 1, 'txt2' union all
select 1, 'txt3' union all
select 2, 'txt2' union all
select 2, 'txt3'
)--测试数据准备完成
select infoid,
STUFF((select '|'+LTRIM(txt) from c1 where infoid = d.infoid
FOR XML PATH('')),1,1,'') as txt
from c1 d
GROUP BY infoid---------------
1 txt1|txt2|txt3
2 txt2|txt3
执行下边这个试试
select t.infoid, replace(stuff((select '+'+cast (txt as nvarchar(100)) from tableA z where t.infoid=z.infoid for xml path('')), 1, 1, ''),'_','' ) as txt
from tableA t group by t.infoid
--或者这个,去掉了以前写的replaceselect t.infoid, stuff((select '+'+cast (txt as nvarchar(100)) from tableA z where t.infoid=z.infoid for xml path('')), 1, 1, '') as txt
from tableA t group by t.infoid