create table student(id int,Name varchar(20)) insert into student select 1,'张三' insert into student select 1,'李四' insert into student select 1,'王五' insert into student select 2,'赵六' insert into student select 2,'邓七' insert into student select 2,'刘八' go
create function gatherItem(@id int) returns varchar(8000) as begin declare @ret varchar(8000) set @ret = '' select @ret = @ret+','+Name from student where id = @id set @ret = stuff(@ret,1,1,'') return @ret end go select * from student
--执行 select id,Name=dbo.gatherItem(id) from student group by id order by id go
--输出结果 /* id Name ---- -------------- 1 张三,李四,王五 2 赵六,邓七,刘八 */
--删除测试数据 drop function gatherItem drop table student go
说白了就是想分组以后 把同一个组的某个字段的值放在一起
insert into student select 1,'张三'
insert into student select 1,'李四'
insert into student select 1,'王五'
insert into student select 2,'赵六'
insert into student select 2,'邓七'
insert into student select 2,'刘八'
go
create function gatherItem(@id int)
returns varchar(8000)
as
begin
declare @ret varchar(8000)
set @ret = ''
select @ret = @ret+','+Name from student where id = @id
set @ret = stuff(@ret,1,1,'')
return @ret
end
go
select * from student
--执行
select id,Name=dbo.gatherItem(id) from student group by id order by id
go
--输出结果
/*
id Name
---- --------------
1 张三,李四,王五
2 赵六,邓七,刘八
*/
--删除测试数据
drop function gatherItem
drop table student
go