Move Values between rows

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

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
   from table1 a
   left join table1 b ON a.rowno-1 = b.rowno
more ▼

asked Oct 31, 2011 at 12:12 PM in Default

avatar image

216 29 32 35

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

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),
 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 
more ▼

answered Oct 31, 2011 at 01:10 PM

avatar image

Magnus Ahlkvist
21.2k 19 39 42

Thanks an update statement will do

Oct 31, 2011 at 03:42 PM sqlnewb
(comments are locked)
10|1200 characters needed characters left
 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
  T2.Cumulative as CumulativeValue,
  T1.OriginalValue as [End]
 from @table1 T1
 cross apply 
  (select sum(OriginalValue) Cumulative from @table1 where RowNo < T1.RowNo) T2


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

answered Nov 01, 2011 at 07:50 AM

avatar image

Kev Riley ♦♦
64.1k 48 62 81

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Oct 31, 2011 at 12:12 PM

Seen: 560 times

Last Updated: Oct 31, 2011 at 12:13 PM

Copyright 2016 Redgate Software. Privacy Policy