SQL函数——将一对多关系转换成一对一关系

我们经常会遇到想要把一对多关系转换成为一对一关系，以方便显示。例如有如下关系：
Class(ClassID,ClassName)和Student(SID,SName,ClassID)，并且，这两个关系存在以下测试数据：

Class:

001	语文
002	数学

Student：

031231301	张三	001
031231301	张三	002
031231302	李四	001

那么，这两个关系表达的意思：选语文的有张三和李四；选数学的有李四。如果想做一个视图（V_STU_CLA）来表达这种一对多关系（一门课程，被多个学生所选择），可以使用一个简单的左联语句来完成：
得到的结果如下：

SELECTC.ClassID,C.ClassName,S.SNameFROMClassC
LEFTJOINStudentSONC.ClassID=S.ClassID;

ClassID	ClassName	SName
001	语文	张三
001	语文	李四
002	数学	张三

这样虽然能够清晰的表达选课关系，但是，某些情况下，它不如下面这种形式来得一目了然：

ClassID	ClassName	SNames
001	语文	张三,李四
002	数学	张三

要达到这样的目的，需要完成一个一对多关系到一对一关系的转换。这样的转换，在数据库中，可以借助函数来进行，因为函数中应用到了游标，故对于Oracle和MSSQL稍有不同，附上两个版本的函数SQL代码：

MS-SQL版：

MS-SQL调用时，通过以下语句实现：
ORACLE中调用方法类似。

SELECTC.ClassID,C.ClassName,dbo.f_getStuNamesByClassID(C.ClassID)
FROMClassC;

--根据课程ID，返回选此课程的学生的名字，以逗号隔开
CREATEfunctiondbo.f_getStuNamesByClassID(@ClassIDint)
RETURNSnvarchar(512)
begin
declare@Resultnvarchar(512);
declare@stuNamenvarchar(256);
Set@Result='';

declarecurcursorfor
(
SELECTS.SNameFROMClassC
LEFTJOINStudentSONC.ClassID=S.ClassID
WHEREC.ClassID=@ClassID;
)
opencur;
fetchnextfromcurinto@stuName;
while(@@fetch_status=0)
begin
set@Result=@Result+@stuName+',';
fetchnextfromcurinto@stuName;
end;
--去除最后多余的一个逗号
IF@Result<>''
SET@Result=SUBSTRING(@Result,1,LEN(@Result)-1);
ELSE
SET@Result=NULL;
return@Result;
end

ORACLE版：

createorreplacefunctionFUN_GET_STUNAMES_BY_CLASSID(P_CLASSIDINVARCHAR2)returnvarchar2is
ResultVARCHAR2(4000);
begin
--通过游标，查找并拼接此课程下的学生姓名
FORCURIN
(
SELECTS.SNameFROMClassC
LEFTJOINStudentSONC.ClassID=S.ClassID
WHEREC.ClassID=@ClassID;
)
LOOP
Result:=Result||CUR.SName||',';
ENDLOOP;
--去掉最后一个逗号
Result:=SUBSTR(Result,0,LENGTH(Result)-1);
return(Result);
end;