How to Display the Previous Row and Next Row value in SELECT statement

 In SQL Server by using LAG & LEAD We can find then Previous & Next row values as below example.

-- create Table

create table Tbl_Test(Id int)

-- Insert some test data into Table

insert into Tbl_Test values (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11)

-- find Current,Previous Row Value

select Id,Lag(Id,1,0) over(Order by Id) as Previous_Value  from Tbl_Test

-- find Current,Next Row Value 

select Id,LEAD(Id,1,0) over(Order by Id) as Next_Value  from Tbl_Test

-- Display Previous,Current, Next row Values

select Lag(Id,1,0) over(Order by Id) as Previous_Value,Id as Current_Value,LEAD(Id,1,0) over(Order by Id) as Next_Value  from Tbl_Test

-- Display 2nd Previous,Current, 2nd Next row Values

select Lag(Id,2,0) over(Order by Id) as '2nd_Previous_Value',Id as Current_Value,LEAD(Id,2,0) over(Order by Id) as '2nd_Next_Value'  from Tbl_Test