sql query to get the next row column as row column

advertisements

I want to write a sql query to get column of the next row to be of column of a row.the test example is as follow: table:

ID   startno
1       1
2       5
3       9

I want to get sql query to get result as follow:

ID   startno  endno
1      1         5
2      5         9
3      9         null


You can do it this way:

WITH CTE AS
(SELECT *,ROW_NUMBER()OVER(ORDER BY ID) AS RN
FROM TableName)

SELECT T1.ID,T1.startno,T2.startno as endno
FROM CTE T1 LEFT JOIN
     CTE T2 ON T1.RN=(T2.RN-1)

You can use ON T1.ID=(T2.ID-1) as well. But if the ID field is not continuous or missing any ID, Join won't work as we exptected. That is why I have used ROW_NUMBER to get a continuous series of numbers to join the tables with.

Result:

ID  startno endno
1   1       5
2   5       9
3   9       (null)

Sample result in SQL Fiddle