sqlserver 用forxmlpath列拼接字符串

?

 
 

--?表结构?
  
  
select?*?from?t;?
  
  
go?
  
  
?
  
  
id?
  
  
0?
  
  
1?
  
  
1?
  
  
5?
  
  
?
  
  
--?用游标自定义的函数?
  
  
alter?function?fn_getName()?
  
  
returns?varchar(255)?
  
  
as?
  
  
begin?
  
  
?
  
  
declare?@name?varchar(50)?,?
  
  
????@strname?varchar(255)?
  
  
declare??cur?cursor?for?select?id?from?t;?
  
  
?
  
  
set?@strname=''?
  
  
open?cur??
  
  
?
  
  
fetch?next?from?cur?into?@name??
  
  
?
  
  
while?@@fetch_status=0?
  
  
begin??
  
  
????set?@strname=@strname+@name?
  
  
?
  
  
????fetch?next?from?cur?into?@name??
  
  
end?
  
  
?
  
  
Close?cur??
  
  
return?@strname?
  
  
end?
  
  
go?
  
  
?
  
  
--?结果如下?
  
  
select?dbo.fn_getName()?
  
  
go?
  
  
?
  
  
(无列名)?
  
  
0115?
  
  
?
  
  
--?用for?xml?path将列拼字符串?
  
  
select?','+cast(id?as?varchar)?from?t??for?xml?path('')?
  
  
go?
  
  
XML_F52E2B61-18A1-11d1-B105-00805F49916B?
  
  
,1,5?
  
  
?
  
  
?
  
  
--?去除最前的逗号?
  
  
select?stuff((select?','+cast(id?as?varchar)?from?t??for?xml?path('')),'')??
  
  
go?
  
  
?
  
  
(无列名)?
  
  
0,5?
 
 

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!