SQL 合并字符串

create table Test
(
ID int,
Value varchar(10)
)

insert Test
values(1,'aa'),
      (1,'bb'),
      (2,'aaa'),
      (2,'bbb'),
      (2,'ccc')

　 1）合并所有的字符串

declare @str varchar(100)
select @str=@str+value+',' from Test
print @str
--结果:aa,bb,aaa,bbb,ccc,

　 2）For XML Path 同组合并字符串

select id,Value=(select Value+',' from Test as b where b.id=a.id for xml path('')) from Test as a

　　结果：

   3) group by 去掉重复

select id,Value=(select Value+',' from Test as b where b.id=a.id for xml path('')) from Test as a
group by id

　  结果：

    4）stuff 填充掉前端的字符

select id,Value=stuff((select ','+Value from Test as b where b.id=a.id for xml path('')),1,1,'') from Test as a
group by id

　  结果：

    STUFF (character_expression, start_position, length,character_expression_insert)  填充掉目标字符串

    5）Replace替换掉所有的空格

select id,Value=replace((select Value as [data()] from Test as b where b.id=a.id for xml path('')),' ',',') from Test as a
group by id

　　结果与4）中的一样

     data()类似数组的作用，返回值 aa bb