Geek asks and answers

Extract hierarchical data from a single column

I have the following data examples:

enter image description here

It basically displays the following hierarchy:

enter image description here

And I should get from him all (S) tart (E) nd pairs similar to this (order is not important):

enter image description here

My solution is to click all the values ​​in the source table variable:

  • Select all auxiliary (S) tart (E) pairs.
  • Insert them into the destination table variable
  • Remove them from the source table variable
  • Follow step 1 if the source table variable is not empty.

My questions is: can anyone come up with an alternative solution? In my real situation, I have more rows and am worried about deleting and pasting into table variables.

Below is the below script: 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')
+3
1

, :

;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)

:

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: sqlserver 2012, script, . , . script , "S" "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
+1

More articles:


All Articles