How to determine next row value using select and case in SQL server?-CodePudding

Using select and case statement in SQL

How to make the 4th column "1" if next row of LETTER column is D. See example below:

https://i.stack.imgur.com/br4dn.png

CodePudding user response：

You can combine CASE with LEAD(). Assuming you are ordering by stepthe query can look like:

select
  t.*,m
  case when lead(letter) over(order by step) = 'D' 
       then 1 else 0 end as is_next_row_letter_d
from t

CodePudding user response：

Assuming that the STEP column provides the ordering and that it is continuous, we could use a self-join approach here:

SELECT t1.STEP, t1.ID, t1.LETTER,
       CASE WHEN t2.LETTER = 'D' THEN 1 ELSE 0 END AS IS_NEXT_D
FROM yourTable t1
LEFT JOIN yourTable t2
    ON t2.STEP = t1.STEP   1
ORDER BY t1.STEP;