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
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments