Data recording mosaic aggregation for SQL Server development

server| data provides some aggregate functions, such as SUM, AVG, COUNT, Max, and Min functions, in SQL Server 2000. However, it is sometimes possible to splice string-type data. For example, the selection of students in the course of a comma-separated display and so on. This requirement is the same as the aggregation provided by SQL Server, which could have been multiple records, and a single field was summarized and then turned into a single record. For example, the Data view of a student's elective course (usually with a student table, a timetable, and a student timetable) is as follows: Study Number selection Course
050301 principles of the database
050301 Operating System
050302 principles of the database
050302 Data structure
050303 Operating System
050303 Data structure
050303 the data required for object-oriented programming may be the following structure: study number selection Course
050301 database principle, operating system
050302 database principle, data structure
050303 operating system, data structure, object-oriented programming to implement this functionality, there are two options, one using cursors and the other using user-defined functions. For simplicity, create a studentcourse table that includes a number and two fields for course selection. Using cursors to implement
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), 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
Using user-defined functions to implement

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
Endgo SELECT DISTINCT studentid,dbo. GetCourse (StudentID)
From
(
SELECT * FROM Studentcourse
) tmptable