--测试环境
Create table 表(name varchar(10),picture varchar(10))
insert into 表 select '河南','aaaa'
union all select '河南','eeee'
union all select '河南','bbbb'
union all select '广东','cccc'
union all select '广东','dddd'
union all select '广东','ffff'
--建函数
CREATE function F_GETSTR(@name varchar(10))
returns varchar(200)
as
begin
declare @return varchar(200)
set @return=''
select @return=@return+','+picture+'' from 表
where name=@name
set @return=stuff(@return,1,1,'')
return @return
end--查询
select name=name,
picture=dbo.F_GETSTR(name)
from 表
group by name--结果
name picture
------ -------
广东 cccc,dddd,ffff
河南 aaaa,eeee,bbbb--删除测试环境
Drop table 表
Drop function F_GETSTR
Create table 表(name varchar(10),picture varchar(10))
insert into 表 select '河南','aaaa'
union all select '河南','eeee'
union all select '河南','bbbb'
union all select '广东','cccc'
union all select '广东','dddd'
union all select '广东','ffff'
--建函数
CREATE function F_GETSTR(@name varchar(10))
returns varchar(200)
as
begin
declare @return varchar(200)
set @return=''
select @return=@return+','+picture+'' from 表
where name=@name
set @return=stuff(@return,1,1,'')
return @return
end--查询
select name=name,
picture=dbo.F_GETSTR(name)
from 表
group by name--结果
name picture
------ -------
广东 cccc,dddd,ffff
河南 aaaa,eeee,bbbb--删除测试环境
Drop table 表
Drop function F_GETSTR
create table t(user_name varchar(16),role_name varchar(30))
insert into t VALUES('系统管理员','管理员')
insert into t VALUES('feng','管理员')
insert into t VALUES('feng','一般用户')
insert into t VALUES('test','一般用户')select * from t
go
create function f_str(@user_name varchar(50))
returns varchar(500)
as
begin
declare @ret varchar(500)
set @ret = ''
select @ret = @ret+','+role_name from t where user_name=@user_name
set @ret = stuff(@ret,1,1,'')
return @ret
end
goselect user_name,role_name=dbo.f_str(user_name) from t group by user_name
drop function f_str
drop table t
create table tab1(name varchar(20),picture varchar(20))
insert into tab1 select '河南','aaaa'
insert into tab1 select '河南','eeee'
insert into tab1 select '河南','bbbb'
insert into tab1 select '广东','cccc'
insert into tab1 select '广东','dddd'
insert into tab1 select '广东','ffff'
--创建测试函数
create function fstr(@a varchar(20))
returns varchar(1000)
as
begin
declare @result varchar(1000)
select @result=''
select @result=@result+','+picture from tab1 where name in(select name from tab1 where name=@a)
select @result=stuff(@result,1,1,'')
return @result
end
go
--测试
select name,dbo.fstr(name)picture from tab1 group by name
--删删测试内容
drop function fstr
drop table tab1
--结果
name picture
广东 cccc,dddd,ffff
河南 aaaa,eeee,bbbb--------------
最近发现提类似这样的问题的人很多。。