SQl Server: Group Adjacent Rows if Certain Column Value is Same.

Below image to the left is original data as in Status change Tracker table. And, what we are expecting is the one to the right. i.e. we want to group the adjacent rows if they are in same stage and need the time stamp when the Stage added for the first time.

To achieve this, below is the simple query needed.

select t.*
from #StatusLog t
left join #StatusLog t1 on t.id = t1.id + 1 and t1.Stage = t.Stage 
where t1.id is null

If we don't have a Column as ID with sequential numbers, use Row_Number () to get the sequential number for rows and execute above script by replacing Id column with it.

Also, if multiple columns should be equal, we can add them along with stage condition in above script.