Hi I have following data in the table as below:
I am looking for below output,
I am using SQL Server 2008 R2.
I just tried with self joining the table with ID1 = ID2+1 and Values not equal conditions.
Select T1.ID as ID1 ,T1.Value V1 ,T2.ID as ID2, T2.Value as V2 From [Test_Seq] T1
INNER Join [Test_Seq] T2
on T2.ID = T1.ID+1
Where T1.Value <>T2.Value
You can try using this query
;with cte as ( select *, row_number() over(partition by value order by id) as rn1, row_number() over(order by id) rn from #startend ) , cte2 as ( select min(id) startid, max(id) as endid, value, rn-rn1 as r from cte group by rn-rn1, value ) select startid, endid, value from cte2 order by startid