declare @sql varchar(1000) set @sql = '' select @sql = @sql + t.col1 + '<'+col2+'>;' from (select col1,col2 from tb) as t exec(@sql)
declare @s varchar(100) ;with tb(col1, col2) as ( select '001', 'a' union all select '002', 'b' union all select '003', 'c' )select @s = isnull(@s,'') + col1 + '<' + col2 + '>;' from tb print @s
declare @s varchar(1000) select @s=isnull(@s,'')+col1+'<'+col2+'>;' from tb select @s
有必要在sql server端进行拼接么,程序端处理不是更简单,更方便么?
create table tb(col1 varchar(10), col2 varchar(10)) insert into tb select '001', 'a' union all select '002', 'b' union all select '003', 'c' godeclare @sql varchar(1000) set @sql = '' select @sql = @sql + t.col1 + '<'+t.col2+'>;' from (select col1,col2 from tb) as t print(@sql) /* 001<a>;002<b>;003<c>; */drop table tb
declare @tb Table( col1 varchar(10), col2 varchar(10) ) insert into @tb select '001','a' union all select '002','b' union all select '003','c'select top 1 stuff((select ';' + [col1]+'<'+[col2]+'>' from @tb for xml path('')) , 1 , 1 , '') from @tb 只可惜xml文件中不认< 和 >
create table tb( col1 varchar(10), col2 varchar(10) ) insert into tb select '001','a' union all select '002','b' union all select '003','c' goselect replace(replace(stuff((select [col1]+'<'+[col2]+'>;' from @tb for xml path('')),1,1,''),'<','<'),'>','>')--001<a>;002<b>;003<c>;
请问是怎么插入SQL code 的?
declare @tb Table( col1 varchar(10), col2 varchar(10) ) insert into @tb select '001','a' union all select '002','b' union all select '003','c'select top 1 stuff((select ';' + [col1]+'<'+[col2]+'>' from @tb for xml path('')) , 1 , 1 , '') from @tb
declare @str nvarchar(1000) set @str='' select top 10 @str=@str+col1+Quotename(col2,'<')+';' from 表 select left(@str,len(@str)-1)这样就OK了
select coll + '<' + col2 + '>' as col3 from tablename
LZ 是想把col1,col2的值串联成一个字符串吧。 select top 1 stuff((select ';' + [col1]+'<'+[col2]+'>' from @tb for xml path('')) , 1 , 1 , '') from @tb这样就行 了。
select replace(replace(stuff((select [col1]+'<'+[col2]+'>;' from tb2 for xml path('')),1,1,(select top 1 left(col1,1) from tb2)),'<','<'),'>','>')
set @sql = ''
select @sql = @sql + t.col1 + '<'+col2+'>;' from (select col1,col2 from tb) as t
exec(@sql)
declare @s varchar(100)
;with tb(col1, col2) as
(
select '001', 'a'
union all
select '002', 'b'
union all
select '003', 'c'
)select @s = isnull(@s,'') + col1 + '<' + col2 + '>;' from tb
print @s
select @s=isnull(@s,'')+col1+'<'+col2+'>;' from tb
select @s
insert into tb select '001', 'a'
union all
select '002', 'b'
union all
select '003', 'c'
godeclare @sql varchar(1000)
set @sql = ''
select @sql = @sql + t.col1 + '<'+t.col2+'>;' from (select col1,col2 from tb) as t
print(@sql)
/*
001<a>;002<b>;003<c>;
*/drop table tb
恩 我也这么考虑了,不是懒吗,呵呵!我以为有高手能够直接提供条SQL 解决问题 就最好了 哈哈
循环一下 呵呵
declare @tb Table(
col1 varchar(10),
col2 varchar(10)
)
insert into @tb
select '001','a' union all
select '002','b' union all
select '003','c'select top 1
stuff((select ';' + [col1]+'<'+[col2]+'>' from @tb for xml path('')) , 1 , 1 , '')
from @tb
只可惜xml文件中不认< 和 >
col1 varchar(10),
col2 varchar(10)
)
insert into tb select '001','a'
union all select '002','b'
union all select '003','c'
goselect replace(replace(stuff((select [col1]+'<'+[col2]+'>;' from @tb for xml path('')),1,1,''),'<','<'),'>','>')--001<a>;002<b>;003<c>;
col1 varchar(10),
col2 varchar(10)
)
insert into @tb
select '001','a' union all
select '002','b' union all
select '003','c'select top 1
stuff((select ';' + [col1]+'<'+[col2]+'>' from @tb for xml path('')) , 1 , 1 , '')
from @tb
set @str=''
select top 10 @str=@str+col1+Quotename(col2,'<')+';' from 表
select left(@str,len(@str)-1)这样就OK了
select top 1
stuff((select ';' + [col1]+'<'+[col2]+'>' from @tb for xml path('')) , 1 , 1 , '')
from @tb这样就行 了。