create table t(A varchar(4),B numeric(5,1)) insert into t select 'A1',2.1 insert into t select 'A1',1.3 insert into t select 'A2',0.6 insert into t select 'A2',8.6 insert into t select 'A3',7.4 insert into t select 'A3',9.1 insert into t select 'A4',5.6 gocreate function f_product(@A varchar(4)) returns numeric(8,4) as begin declare @product numeric(8,4) set @product=1.0
select @product=@product*B from t where A=@A
return @product end goselect A,dbo.f_product(A) as product from t group by A /* A product ---- ---------- A1 2.7300 A2 5.1600 A3 67.3400 A4 5.6000 */ godrop function f_product drop table t go
insert into t select 'A1',2.1
insert into t select 'A1',1.3
insert into t select 'A2',0.6
insert into t select 'A2',8.6
insert into t select 'A3',7.4
insert into t select 'A3',9.1
insert into t select 'A4',5.6
gocreate function f_product(@A varchar(4))
returns numeric(8,4)
as
begin
declare @product numeric(8,4)
set @product=1.0
select @product=@product*B from t where A=@A
return @product
end
goselect A,dbo.f_product(A) as product from t group by A
/*
A product
---- ----------
A1 2.7300
A2 5.1600
A3 67.3400
A4 5.6000
*/
godrop function f_product
drop table t
go