自定义函数实现字符串中数据的运算和统计

问题:
数据表如下
cate            values                                  dates
 A1           12.32,15.6,14.5,45.00,78.12,23.45         2006-04-01
 A2           14.56,67.23,45.12,23.1,34.67,56.98        2006-04-01
 A1           12.31,15.6,14.5,44.00,78.12,23.45         2006-04-01
 A2           14.56,67.23,45.12,23.1,34.67,56.98        2006-04-01
 A1           12.32,15.6,14.5,45.00,78.12,23.45         2006-05-01
 A2           14.56,67.23,45.12,23.1,34.67,56.98        2006-05-01
 A1           12.31,15.6,14.5,44.00,78.12,23.45         2006-05-01
 A2           14.56,67.23,45.12,23.1,34.67,56.98        2006-05-01
不用存储过程实现如下数据统计
 cate            T1                                     dates
  A1          24.63,31.2,29.0,89.0,156.24,46.9         2006-04-01
  A1          24.63,31.2,29.0,89.0,156.24,46.9         2006-05-01
  A2          29.12,134.46,90.24,46.2,69.34,113.96     2006-04-01
  A2          29.12,134.46,90.24,46.2,69.34,113.96     2006-05-01
==========================================================================
就是 values 值之间用,隔开的,当cate和dates 相同时,算values中各个以,隔开的数值的累加,要求是不用存储过程实现

--建立
create table up(
cate  varchar(3),
[values]  varchar(50),
dates datetime
)

insert up select
 'A1','12.32,15.6,14.5,45.00,78.12,23.45','2006-04-01'
union all select
 'A2','14.56,67.23,45.12,23.1,34.67,56.98','2006-04-01'
union all select
 'A1','12.31,15.6,14.5,44.00,78.12,23.45','2006-04-01'
union all select
 'A2','14.56,67.23,45.12,23.1,34.67,56.98','2006-04-01'
union all select
 'A1','12.32,15.6,14.5,45.00,78.12,23.45','2006-05-01'
union all select
 'A2','14.56,67.23,45.12,23.1,34.67,56.98','2006-05-01'
union all select
 'A1','12.31,15.6,14.5,44.00,78.12,23.45','2006-05-01'
union all select
 'A2','14.56,67.23,45.12,23.1,34.67,56.98','2006-05-01'

go

--加法函数
create function fn_Plus(
@s1 varchar(50),
@s2 varchar(50)
)
returns varchar(50)
as
begin
   if @s1 is null or @s1=''
       return @s2
   if @s2 is null or @s2=''
       return @s1
   declare @r varchar(50)
   set @r=''
   declare @f1 float
   declare @f2 float
   while charindex(',',@s1)>0 and charindex(',',@s2)>0
   begin
        set @f1=left(@s1,charindex(',',@s1)-1)
 set @f2=left(@s2,charindex(',',@s2)-1)
 set @s1=stuff(@s1,1,charindex(',',@s1),'')
 set @s2=stuff(@s2,1,charindex(',',@s2),'')
 set @r=@r+','+cast(@f1+@f2 as varchar)
   end
   if @s1<>'' and @s2<>''
   set @f1=@s1
   set @f2=@s2
   set @r=@r+','+cast(@f1+@f2 as varchar)
   if @r<>''
      set @r=stuff(@r,1,1,'')
   return @r
end
go

--求值函数
create function fn_Values(
@cate  varchar(3),
@dates datetime
)
returns varchar(50)
as
begin
   declare @r varchar(50)
   set @r=''
   select @r=dbo.fn_Plus(@r,[values]) from up where cate=@cate and dates=@dates
   return @r
end
go

--查询
select cate,dbo.fn_values(cate,dates) as T,dates
from up
group by cate,dates

--结果
cate T                                                  dates                                                 
---- -------------------------------------------------- ------------------------------------------------------
A1   24.63,31.2,29,89,156.24,46.9                       2006-04-01 00:00:00.000
A1   24.63,31.2,29,89,156.24,46.9                       2006-05-01 00:00:00.000
A2   29.12,134.46,90.24,46.2,69.34,113.96               2006-04-01 00:00:00.000
A2   29.12,134.46,90.24,46.2,69.34,113.96               2006-05-01 00:00:00.000

（所影响的行数为 4 行）