How to create a column with the next value in the list

Question

I have a SQL table as such

id	date	value
1	01/01/2019	50
1	04/01/2019	25
2	01/01/2019	63
2	15/01/2019	43

I want to create another column called next_date, and next_value that computes the next date and value grouped by id in the list, so that the new table will look:

id	date	value	next_date	next_value
1	01/01/2019	50	04/01/2019	25
1	04/01/2019	25	None	None
2	01/01/2019	63	15/01/2019	43
2	15/01/2019	43	None	None

Answer 1

You may use the LEAD() analytic function here:

SELECT id, date, value,
       COALESCE(CONVERT(varchar, LEAD(date) OVER (PARTITION BY id ORDER BY date), 103), 'None') next_date,
       COALESCE(CAST(LEAD(value) OVER (PARTITION BY id ORDER BY date) AS varchar(max)), 'None') AS next_value
FROM yourTable
ORDER BY id, date;

Demo