参考以下示例:--生成测试数据 create table 表(部门 int,人员 varchar(20)) insert into 表 select 1,'张三' insert into 表 select 1,'李四' insert into 表 select 1,'王五' insert into 表 select 2,'赵六' insert into 表 select 2,'邓七' insert into 表 select 2,'刘八' go--创建用户定义函数 create function f_str(@department int) returns varchar(8000) as begin declare @ret varchar(8000) set @ret = '' select @ret = @ret+','+人员 from 表 where 部门 = @department set @ret = stuff(@ret,1,1,'') return @ret end go --执行 select 部门,人员=dbo.f_str(部门) from 表 group by 部门 order by 部门 go--输出结果 /* 部门 人员 ---- -------------- 1 张三,李四,王五 2 赵六,邓七,刘八 */ --删除测试数据 drop function f_str drop table 表 go
create table lo(id int,name varchar(10)) insert into lo select 1,'a' insert into lo select 1,'b' insert into lo select 1,'c' insert into lo select 2,'d' insert into lo select 2,'e' insert into lo select 3,'f'create function wsp5(@id int) returns varchar(50) as begin declare @sql varchar(8000) select @sql=isnull(@sql+',','')+name from lo where id=@id return @sql end 调用函数: select distinct id,dbo.wsp5(id) as name from lo
create table tb (id int ,name char(10)) insert tb select 1 ,'a' union all select 1 ,'b' union all select 1 ,'c' union all select 2 ,'d' union all select 2 ,'e' union all select 3, 'f' select * from tb alter function union_id(@id int ) returns varchar(8000) as begin declare @str varchar(1000) set @str = '' select @str = @str+','+ rtrim(name) from tb where id = @id set @str=right(@str,len(@str)-1) return @str end goselect id,dbo.union_id(id) from tb group by id
select distinct id,dbo.union_id(id) as name from tb id name 1 a,b,c 2 d,e 3 f
create table 表(部门 int,人员 varchar(20))
insert into 表 select 1,'张三'
insert into 表 select 1,'李四'
insert into 表 select 1,'王五'
insert into 表 select 2,'赵六'
insert into 表 select 2,'邓七'
insert into 表 select 2,'刘八'
go--创建用户定义函数
create function f_str(@department int)
returns varchar(8000)
as
begin
declare @ret varchar(8000)
set @ret = ''
select @ret = @ret+','+人员 from 表 where 部门 = @department
set @ret = stuff(@ret,1,1,'')
return @ret
end
go
--执行
select 部门,人员=dbo.f_str(部门) from 表 group by 部门 order by 部门
go--输出结果
/*
部门 人员
---- --------------
1 张三,李四,王五
2 赵六,邓七,刘八
*/
--删除测试数据
drop function f_str
drop table 表
go
create table lo(id int,name varchar(10))
insert into lo select 1,'a'
insert into lo select 1,'b'
insert into lo select 1,'c'
insert into lo select 2,'d'
insert into lo select 2,'e'
insert into lo select 3,'f'create function wsp5(@id int)
returns varchar(50)
as
begin
declare @sql varchar(8000)
select @sql=isnull(@sql+',','')+name from lo where id=@id
return @sql
end
调用函数:
select distinct id,dbo.wsp5(id) as name from lo
insert tb select 1 ,'a' union all select
1 ,'b' union all select
1 ,'c' union all select
2 ,'d' union all select
2 ,'e' union all select
3, 'f'
select * from tb alter function union_id(@id int )
returns varchar(8000)
as
begin
declare @str varchar(1000)
set @str = ''
select @str = @str+','+ rtrim(name) from tb where id = @id
set @str=right(@str,len(@str)-1)
return @str
end
goselect id,dbo.union_id(id) from tb group by id
1 a,b,c
2 d,e
3 f