Extract hierarchical data from one column

Question

I have the following sample data:

It basically display the following hierarchy:

And I should get from it all (S)tart (E)nd pairs like this(the order is not important):

My solution is to push all values in "Source" table variable:

1. Select all consecuensive (S)tart (E)nd pairs
2. Insert them into a "Destination" table variable
3. Delete them from the "Source" table variable
4. Execute step 1 if "Source" table variable is not empty

My questions is can someone came up with alternative solution? In my real situation I have more rows and I am worry about deleting and inserting in table variables.

The script below generates sample data:

DECLARE @DataSource TABLE
(
    [ID] TINYINT
   ,[Type] CHAR(1)
)

INSERT INTO @DataSource ([ID], [Type])
VALUES (3,'S')
      ,(6,'E')
      ,(7,'S')
      ,(10,'S')
      ,(13,'E')
      ,(14,'E')
      ,(15,'S')
      ,(16,'S')
      ,(17,'S')
      ,(19,'S')
      ,(20,'S')
      ,(26,'E')
      ,(27,'E')
      ,(28,'E')
      ,(29,'E')
      ,(30,'E')
      ,(31,'S')
      ,(32,'S')
      ,(35,'E')
      ,(36,'S')
      ,(38,'E')
      ,(39,'S')
      ,(40,'S')
      ,(44,'E')
      ,(45,'E')
      ,(46,'E')

Answer 1

It is a bit complicated, but try this:

;with a as
(
  select [ID], [Type], row_number() over (order by ID) rn from @DataSource
), b as
(
  select [ID], [Type], rn, cast(1 as int) lvl from a where rn = 1
  union all
  select a.[ID], a.[Type],a.rn, case when a.[Type] = 'S' then 1 when a.[Type] = 'E' then -1 else 0 end + lvl 
  from a
  join b on a.rn = b.rn + 1
), 
c as
(
  select [ID], [Type], row_number() over (partition by [type] order by lvl,rn) grp
  from b
)
select c1.id S, c2.id E from c c1
join c c2
on c1.grp = c2.grp
where c1.[type] = 'S' and c2.[type] = 'E'
order by c1.id
option( maxrecursion 0) 

Result:

S   E
3   6
7   14
10  13
15  30
16  29
17  28
19  27
20  26
31  46
32  35
36  38
39  45
40  44

EDIT: because you are using sqlserver 2012, the script can be simplified, I also added an improvement for performance. I hope this works in your case. Script now assume that 'S' is always before 'E'.

;with a as
(
  select [ID], [Type], 
  sum(case when [type]='S' then 1 when [type]='E' then -1 end)over(order by id) lvl
  from @DataSource
), b as
(
  select [ID], [Type], 
  row_number() over (partition by [type] order by lvl,id) grp
  from a
)
select min(id) S, max(id) E 
from b
group by grp
order by s