如下例:
CREATE FUNCTION dbo.f_str(@name varchar(10))
RETURNS varchar(100)
AS
BEGIN
DECLARE @re varchar(100)
SET @re=''
SELECT @re=@re+'+'+course
FROM try1
where [name]=@name
RETURN(STUFF(@re,1,1,''))
END
红体部分会对相应参数的记录进行一个循环相加
select [name],
course=dbo.f_str([name]) ,
sum(grade) '成绩'
from try1
group by [name]
效果如下:
1 one cn 78.0
2 one en 90.0
3 two cn 45.0
4 three en 98.0
5 one arth 90.0
6 two en 76.0
7 two arth 89.0
8 three cn 87.0
9 three math 54.0
one cn+en+arth 258.0
three en+cn+math 239.0
two cn+en+arth 210.0