--创建用户定义函数
create function f_str(@psid int)
returns varchar(8000)
as
begin
declare @ret varchar(8000)
set @ret = ''
select @ret = @ret+','+name from 表 where psid = @psid
set @ret = stuff(@ret,1,1,'')
return @ret
end
go--执行查询
select distinct dbo.f_str(psid) from 表
go
create function f_str(@psid int)
returns varchar(8000)
as
begin
declare @ret varchar(8000)
set @ret = ''
select @ret = @ret+','+name from 表 where psid = @psid
set @ret = stuff(@ret,1,1,'')
return @ret
end
go--执行查询
select distinct dbo.f_str(psid) from 表
go
returns varchar(8000)
as
begin
declare @str varchar(8000)
set @str=''
select @str=@str+','+name from 表 where psid=@psid
return stuff(@str,1,1,'')
end
go--查询
select distinct dbo.f_str(psid) from 表 group by psid
create table t(id int,psid int,name varchar(80))
insert into t select 1,1,'name1'
insert into t select 2,1,'name2'
insert into t select 3,2,'name1'
insert into t select 4,2,'name2'
insert into t select 5,3,'name1'
insert into t select 6,5,'name1'
insert into t select 7,6,'name1'
insert into t select 8,7,'name4'
go--创建用户定义函数
create function f_str(@psid int)
returns varchar(8000)
as
begin
declare @ret varchar(8000)
set @ret = ''
select @ret = @ret+','+name from t where psid = @psid
set @ret = stuff(@ret,1,1,'')
return @ret
end
go--执行查询
select distinct name=dbo.f_str(psid) from t
go--输出结果
/*
name
-----------
name1
name1,name2
name4
*/
--删除测试数据
drop function f_str
drop table t
(
id int, psid int,name varchar(10)
)
insert A
select 1,1,'name1' union
select 2,1,'name2' union
select 3,2,'name1' union
select 4,2,'name2' union
select 5,3,'name1' union
select 6,5,'name1' union
select 7,6,'name1' union
select 8,7,'name4'
gocreate function f_str(@psid int)
returns varchar(8000)
as
begin
declare @str varchar(8000)
set @str=''
select @str=@str+','+name from A where psid=@psid
return stuff(@str,1,1,'')
end
go--查询
select distinct dbo.f_str(psid) as 'name' from A group by psid--删除测试环境
drop function f_str
drop table A--结果
/*
name
----------------
name1
name1,name2
name4(所影响的行数为 3 行)
*/