How to do in SQL: next and privious value with same value of master_id from two rows? MS SQL Server-CodePudding

Hello every Body

I have a table like this in SQL:

I need to write SQL query to get the result as the following:

Thanks for any help in advance

CodePudding user response：

Do a self join on master_id and exclude rows where the values match:

with cte as (
select 1 [details_id], 1 [master_id], 'a' [value]
union all select 2, 1, 'b'
union all select 3, 2, 'c'
union all select 4, 2, 'd'
)

select a.*, b.value 
from cte a
inner join cte b on a.master_id = b.master_id 
where a.value <> b.value

CodePudding user response：

Thanks @Simeon SQL get value from next row guide me The answer will be: select details_id,master_id,value,CASE WHEN LEAD( value) OVER ( PARTITION BY master_id ORDER BY details_id ) IS NOT NULL THEN LEAD( value) OVER ( PARTITION BY master_id ORDER BY details_id ) else lag( value) OVER ( PARTITION BY master_id ORDER BY details_id ) END AS second_value from table1