In SQL Server how to shift rows for two columns [closed]

This is the original table

Name Marks_Original
Anil 10
Ravi 20
Pooja 30
Sheela 40
Rohit 50
Vijay 60

I want the output using 2 calculated columns as below where my 1st column record is static but my 2nd column records are shifting up and down respective to 1st column

Name Marks_1 Marks_2
Anil 0 20
Ravi 10 30
Pooja 20 40
Sheela 30 50
Rohit 40 60
Vijay 50 0

1 Answer

Try the window functions (assuming the sequence is Marks_Original)

Select * ,Marks_1 = Lag(Marks_Original,1,0) over (Order By Marks_Original) ,Marks_2 = lead(Marks_Original,1,0) over (Order By Marks_Original) From YourTable Order By Marks_Original

Returns

Name Marks_Original Marks_1 Marks_2
Anil 10 0 20
Ravi 20 10 30
Pooja 30 20 40
Sheela 40 30 50
Rohit 50 40 60
Vijay 60 50 0