adding a value to a column from data in next row sql

Question

Base Table

id line_number
1   1232
2   1456
3   1832
4   2002

I wish to add values to a new table such that the next row's value becomes the value in a new column with the last row's value being same..

The final output I need to produce is:

id line_number   end_line_number
1   1232         1456
2   1456         1832
3   1832         2002
4   2002         2002

The database is sql server.

Any help is sincerely appreciated.

Thanks

Answer 1

After SQL Server 2012, you can use LEAD like this.

;WITH BaseTable as 
(
    SELECT 1 id,  1232 line_number
    UNION ALL SELECT 2 ,  1456
    UNION ALL SELECT 3,   1832
    UNION ALL SELECT 4 ,  2002
)
SELECT id,line_number,(LEAD(line_number,1,line_number) OVER(ORDER BY id ASC))
FROM BaseTable

For previous versions, try this

;WITH BaseTable as 
(
    SELECT 1 id,  1232 line_number
    UNION ALL SELECT 2 ,  1456
    UNION ALL SELECT 3,   1832
    UNION ALL SELECT 4 ,  2002
), OrderedBaseTable as 
(
SELECT id,line_number,ROW_NUMBER() OVER(ORDER BY id asc) rw
FROM BaseTable
)
SELECT t1.id,t1.line_number,ISNULL(t2.line_number,t1.line_number) next_line_number
FROM OrderedBaseTable t1
LEFT JOIN OrderedBaseTable t2
ON t1.rw = t2.rw - 1

Answer 2

Try this

 With T as (
    Select id, line_number, Row_Number() OVER(Order By id) + 1 As TempId From TableName)

    Select T1.id, T1.line_number, ISNULL(T2.line_number,T1.line_number) As end_line_number From T T1
    Left Join T T2 on T2.id = T1.TempId

SQL Fiddle Demo