Geek Answers Handbook

Adding a value to a column from data in the next sql row

Base table

id line_number 1 1232 2 1456 3 1832 4 2002

I want to add values ​​to a new table so that the next row value becomes the value in the new column, with the last row value being the same.

The end result I need to create is:

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

The database is a sql server.

Any help is truly appreciated.

thanks

+7
sql sql-server
source share
2 answers

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
+7
source share

try it

  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

+1
source share

More articles:


All Articles