在SQL Server 2000 中提供了一些聚合函数,例如SUM、AVG、COUNT、MAX和MIN函数。然而有时候可能要对字符串型的数据进行拼接。例如,把学生的选课情况以逗号分割进行显示等等。
这种需求与SQL Server提供的聚合具有同一个性质,都是原本可能是多个记录,按某一个字段经过汇总处理后变成一条记录。
例如学生选课的数据视图(通常是会有学生表、课程表、学生选课表关联而成)中的数据如下:
学号 选择课程
050301 数据库原理
050301 操作系统
050302 数据库原理
050302 数据结构
050303 操作系统
050303 数据结构
050303 面向对象程序设计
而需要的数据可能是如下的结构:
学号 选择课程
050301 数据库原理,操作系统
050302 数据库原理,数据结构
050303 操作系统,数据结构,面向对象程序设计
要实现这种功能,可以有两种选择,一种使用游标,另一种方法是使用用户自定义函数。为了简单,下面就创建一个StudentCourse表,该表包括学号和选择课程两个字段。
使用游标来实现
declare C1 cursor for
select StudentId,CourseName from StudentCourse
declare @StudentId varchar(10)
declare @CourseName varchar(50)
declare @Count int
if object_id('TmpTable') is not null
drop table TmpTable
create table TmpTable(StudentId varchar(10),CourseName varchar(1024))
open C1
fetch next from C1 into @StudentId,@CourseName
while @@FETCH_STATUS = 0
begin
select @Count = count(*) from TmpTable where StudentId=@StudentId
if @Count = 0
insert into TmpTable select @StudentId, @CourseName
else
update TmpTable Set CourseName = CourseName + ',' + @CourseName where StudentId=@StudentId
fetch next from C1 ino @StudentId,@CourseName
end
close C1
deallocate C1
select * from TmpTable order by StudentId
使用用户自定义函数来实现
create function GetCourse(@StudentId varchar(10))
returns varchar(4000)
as
begin
declare @s nvarchar(4000)
set @s=''
select @s=@s+','+ CourseName from StudentCourse
where @StudentId=StudentId
set @s=stuff(@s,1,1,'')
return @s
end
go
select distinct StudentId,dbo.GetCourse(StudentId)
from
(
select * from StudentCourse
) TmpTable