SQL Server 开发之 数据记录拼接聚合

在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 [email protected]
    if @count = 0
      insert into tmptable select @studentid, @coursename     
    else
      update tmptable set coursename = coursename + ',' + @coursename where [email protected]    
    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   @[email protected]+','+ 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