Move Values between rows

Question

I am trying to write a query which would allow me to add two columns and then shift the number to the row below. An example will better explain what I am doing.

RowNo CumulativeValue OriginalValue
1        NULL            100
2        100             20
3        120             50
4        170             -40
5        130             20
  etc....

I thought this would work but it just is adding the two columns next to each other. Any thoughts??

select  a.rowno,
       (a.CumulativeValue+a.OriginalValue), as CumulativeValue
       a.OrginalValue
  from table1 a
  left join table1 b ON a.rowno-1 = b.rowno

Answer 1

My brain refuses to function normally at this time, so I couldn't find a way to do it with a SELECT. But since I did it with an UPDATE, I really can't see how it can be hard to do with a single SELECT. The problem for me is that it's not possible to do @i = @i + t2.OriginalValue and data retrieval in the same SELECT.

Anyway, here's my UPDATE that does what you're looking for.

declare @t table(rowno int PRIMARY KEY, OriginalValue int, CumulativeValue int)
insert into @t  (rowno, OriginalValue)
values       (1,100),
          (2,20),
          (3,50),
          (4,-40),
          (5,20)

declare @i int
set @i=0

UPDATE t  SET t.CumulativeValue = @i, @i=@i + t2.OriginalValue
FROM @t t inner join @t t2 ON t.rowno=t2.rowno +1

select rowno,CumulativeValue,OriginalValue  from @t

Answer 2

declare @table1 table (RowNo int, OriginalValue int)

insert into @table1 select 1,100
insert into @table1 select 2,20
insert into @table1 select 3,50
insert into @table1 select 4,-40
insert into @table1 select 5,20

select
 T1.RowNo,
 T2.Cumulative as CumulativeValue,
 T1.OriginalValue as [End]
from @table1 T1
cross apply 
 (select sum(OriginalValue) Cumulative from @table1 where RowNo < T1.RowNo) T2

gives

RowNo       CumulativeValue End
----------- --------------- -----------
1           NULL            100
2           100             20
3           120             50
4           170             -40
5           130             20

Move Values between rows

Follow this question

Related Questions