how do I write a query in SQL to do this?
name mark u 23 h 38 j 20 p 83
name mark mark2 u 23 38 h 38 20 j 20 83 p 83 0
The query to accomplish the task: is
;WITH Test (ID, Name, Marks) AS ( SELECT ID,[name],[Marks] FROM tbl_Rep )SELECT A.[ID],A.[Name],A.[Marks],ISNULL(B.[Marks],0) as [Marks2] FROM Test A Left Join [tbl_Rep] B On A.[ID]+1=B.[ID]
Here in this code we have created a temporary table using Common Table Expression and using self join we have joined two instances of a same table with id of one equals to the one plus id. Thats’ the whole process is this.
we can do it with the help of loop but join will be the more efficient technique. Also, in this script IsNull() can be replaced by Coalesce() function.