mssql sqlserver 使用for xml path对字符串进行拼接的方法分享


摘要:
下文通过举例的方式分享 对一个表中相同value值后的多行字符串数据进行拼接,采用逗号分隔,
实现思路:使用for xml path 和stuff函数实现
如下所示:
实验环境:sqlserver 2008 R2


 create table test(keyid int,info varchar(100))
go

insert into test(keyId,info)values(1,'maomao365.com')
insert into test(keyId,info)values(1,'b')
insert into test(keyId,info)values(2,'猫猫小屋')
insert into test(keyId,info)values(2,'sql_blog')
insert into test(keyId,info)values(3,'sql技巧网站')

go

---数据拼接方法分享 for xml 

select keyId,STUFF((select ','+ info  from dbo.test   where dbo.test.keyId=b.keyId for xml path('')),1,1,'')as info
from dbo.test as b group by keyId

go
truncate table test 
drop table test 
mssql_sqlserver字符串拼接技巧分享

mssql_sqlserver字符串拼接技巧分享