Find out the values between a range in SQL Server 2005(SET BASED APPROACH)?

I have a table like

Id  Value
1   Start
2   Normal
3   End
4   Normal
5   Start
6   Normal
7   Normal
8   End
9   Normal

I have to bring the output like

id  Value
1   Start
2   Normal
3   End

5   Start
6   Normal
7   Normal
8   End

i.e. the records between Start & End. Records with id's 4 &开发者_运维百科amp; 9 are outside the Start & End henceforth are not there in the output.

How to do this in set based manner (SQLServer 2005)?

Load a table @t:

declare @t table(Id int,Value nvarchar(100));
insert into @t values (1,'Start'),(2,'Normal'),(3,'End'),(4,'Normal'),(5,'Start'),(6,'Normal'),(7,'Normal'),(8,'End'),(9,'Normal');

Query:

With RangesT as (
    select Id, (select top 1 Id from @t where Id>p.Id and Value='End' order by Id asc) Id_to
    from @t p
    where Value='Start'
)
select crossT.* 
from RangesT p
cross apply (
    select * from @t where Id>=p.Id and Id<=Id_to
) crossT
order by Id

Note that I'm assuming no overlaps. The result:

Id          Value
----------- ------
1           Start
2           Normal
3           End
5           Start
6           Normal
7           Normal
8           End