Recursive multiplication MS SQL

November 28, 2021

I have two values. How can I multiply the first value by the second, then multiply the result of this multiplication by the first number again, then multiply the result by the first number again, and so on? The number of such repetitions for example 7

I have two Columns A and B.
A: 1.65
B: 10

And I need result like this in Result column:
Result: 16,5, 27,2, 44,9, 74,1, 122,2, 201,7, 332,9

So the multiplication would be:

1.65*10=16,5

1.65*16,5=27,2

1.65*27,2=44,9

etc…

I guess I should use a recursive query but I am not sure how to write it I am using MS SQL.

>Solution :

Recursive solution

with CTE as (
  select 
   1.65 as val1,
   10 as val2
)
, RCTE as (
  select 
   val1,
   val2,
   1 as lvl,
   cast(val1*val2 as decimal(20,14)) as result
  from CTE
  union all
  select val1, val2, lvl+1
  , cast(val1*result as decimal(20,14))
  from RCTE
  where lvl < 7
)
select *
from RCTE
GO

val1 | val2 | lvl |             result
---: | ---: | --: | -----------------:
1.65 |   10 |   1 |  16.50000000000000
1.65 |   10 |   2 |  27.22500000000000
1.65 |   10 |   3 |  44.92125000000000
1.65 |   10 |   4 |  74.12006250000000
1.65 |   10 |   5 | 122.29810312500000
1.65 |   10 |   6 | 201.79187015625000
1.65 |   10 |   7 | 332.95658575781250

db<>fiddle here