假如表中有如下内容:
id name
------------
1 zhang
1 li
1 wang那么select name from table where id=1 会返回3条记录,如果我只想返回一条,并且name的值是以","隔开,即返回(zhang,li,wang),是否可行?
id name
------------
1 zhang
1 li
1 wang那么select name from table where id=1 会返回3条记录,如果我只想返回一条,并且name的值是以","隔开,即返回(zhang,li,wang),是否可行?
set @str = ''select @str = @str + ',' + name from table where id = 1
set @str = stuff(@str,1,1,'')
select @str
returns varchar(200)
as
begin
declare @str varchar(200)
set @str=''
select @str=@str+','+[name] from T where id=@id
set @str=stuff(@str,1,1,'')
return @str
endGoselect id,dbo.fn_str(id) as [name]
from T
group by id
returns varchar(8000)
as
begin
declare @str varchar(8000)
set @str = ''
select @str = @str +','+ name from tablename where id= @a
set @str = right(@str , len(@str) - 1)
return(@str)
End
go
select distinct id ,dbo.f_hb(id) as b from tablename
insert T select 1, 'zhang'
union all select 1, 'li'
union all select 1, 'wang'create function fun(@id int)
returns varchar(1000)
as
begin
declare @re varchar(1000)
set @re=''
select @re=@re+name+',' from T where id=@id
set @re=left(@re, len(@re)-1)
return @re
end select distinct id, dbo.fun(id) as name from Tid name
----------- ----------
1 zhang,li,wang(1 row(s) affected)