x

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

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

sqlnewb gravatar image

sqlnewb
216 28 30 31

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

answered Oct 31, 2011 at 01:10 PM

Magnus Ahlkvist gravatar image

Magnus Ahlkvist
16.6k 17 20 33

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

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

answered Nov 01, 2011 at 07:50 AM

Kev Riley gravatar image

Kev Riley ♦♦
53.9k 47 49 76

(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.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

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

By RSS:

Answers

Answers and Comments

SQL Server Central

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

Topics:

x1850
x991

asked: Oct 31, 2011 at 12:12 PM

Seen: 463 times

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