表如下id username
1 aa
2 bb
3 cc
4 dd
2 ee
3 ff
2 gg我想得到这样的结果id username
1 aa
2 bb,ee,gg
3 cc,ff
4 dd用sql能实现吗?
1 aa
2 bb
3 cc
4 dd
2 ee
3 ff
2 gg我想得到这样的结果id username
1 aa
2 bb,ee,gg
3 cc,ff
4 dd用sql能实现吗?
---------------------------------------------------------------------------------------
--生成测试数据
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
returns varchar(8000)
as
begin
declare @ret varchar(8000)
set @ret = ''
select @ret = @ret+','+username from tab where id = @id
set @ret = stuff(@ret,1,1,'')
return @ret
end
go
select id,username=dbo.f_str(id)
from tab
group by id
order by id
create table tab(id int,username varchar(20))
insert into tab select 1,'aa'
insert into tab select 2,'bb'
insert into tab select 3,'cc'
insert into tab select 4,'dd'
insert into tab select 2,'ee'
insert into tab select 3,'ff'
insert into tab select 2,'gg'
gocreate function f_str(@id int)
returns varchar(8000)
as
begin
declare @ret varchar(8000)
set @ret = ''
select @ret = @ret+','+username from tab where id = @id
set @ret = stuff(@ret,1,1,'')
return @ret
end
go
select id,username=dbo.f_str(id)
from tab
group by id
order by id
create table ta(id int,username varchar(20))
insert into ta select 1,'aa'
insert into ta select 2,'bb'
insert into ta select 3,'cc'
insert into ta select 4,'dd'
insert into ta select 2,'ee'
insert into ta select 3,'ff'
insert into ta select 2,'gg'create function test(@id int)
returns varchar(8000)
as
begin
declare @s varchar(8000)
select @s = isnull(@s,'')+','+username from ta where id = @id
return stuff(@s,1,1,'')
endselect distinct id,dbo.test(id)username from ta
id username
----------- -------------------
1 aa
2 bb,ee,gg
3 cc,ff
4 dd(所影响的行数为 4 行)
--drop table ta
--drop function test