How To Continously Add Values Of Starting Row And Next Row To It

i just want to create an sql query and the result is something like on the image., something like Fibonacci sequence in SQL. Ex. Column 1: 10 , then the value of Result column is R

Solution 1:

If you are using MSSQL2012 or higher you can use OVER clause.

SELECT t2.id, t2.value, SUM(t2.value) OVER (ORDERBY t2.id) as [Result]
FROM   Test01 t2
ORDERBY t2.id;

sql fiddle demo

Solution 2:

You can try this

CREATETABLE #TEST(ID INT,VALUEINT)
INSERTINTO #TEST VALUES
(1,10),(2,20),(3,30),(4,40),(5,50),(6,60),(7,70)

;WITH CTE
as
(
   SELECT ID,VALUE,VALUEASRESULTFROM #TEST WHERE ID=1UNIONALLSELECT T.ID,T.VALUE,T.VALUE+C.RESULT
   FROM #TEST T INNERJOIN CTE C ON T.ID = C.ID+1
)

SELECT*FROM CTE

Solution 3:

Try this

select Id, value,
(selectsum(t2.value) from TEST01 t2 where t2.id <= t1.id ) 
as Result
from TEST01 t1

Find the solution in fiddle http://sqlfiddle.com/#!6/a8f56/2

Solution 4:

You could also use a window function.

DECLARE@myTableTABLE(ID INT, val INT);

INSERTINTO@myTableVALUES (1,10),
                            (2,7),
                            (3,-4),
                            (4,1);
SELECT ID, 
       val,
       SUM(val) OVER (ORDERBY ID 
                      ROWSBETWEEN UNBOUNDED PRECEDING 
                      ANDCURRENTROW) ASresultFROM@myTableORDERBY ID;

ID  val  result--  ---  ------1101027173-4134114