SQL Server 2022

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table #chalk (
    sidewalkId int,
    pstart int,
    pend int,
    color varchar(50)
);
insert into #chalk values
(1, 0, 5, 'blue'),
(1, 5, 10, 'blue'),
(1, 10, 15, 'blue'),
--(1, 15, 20, null),--nulls may be explicit or implicit
(1, 20, 25, 'blue'),
--(1, 25, 30, null),--nulls may be explicit or implicit
(1, 30, 35, 'blue'),
(1, 35, 40, 'blue'),
(1, 0, 5, 'red'),
(1, 5, 10, 'red'),
(1, 10, 15, 'red'),
(1, 30, 35, 'red')
,(2, 0, 10, 'blue')
,(2, 10, 20, 'red')
,(2, 10, 25, 'green')
,(2, 20, 25, 'orange')
,(2, 25, 35, 'yellow')
;
select * from #chalk;


sidewalkId
pstart
pend
color


1
0
5
blue

1
5
10
blue

1
10
15
blue

1
20
25
blue

1
30
35
blue

1
35
40
blue

1
0
5
red

1
5
10
red

1
10
15
red

1
30
35
red

2
0
10
blue

2
10
20
red

2
10
25
green

2
20
25
orange

2
25
35
yellow


with ranges as(
select p.sidewalkid,p.point,p.next,c.pstart,c.pend,c.color
from(
  select *
  ,lead(point)over(partition by sidewalkid order by point) next
from(
select sidewalkid,pstart point from #chalk
union 
select sidewalkid,pend point from #chalk
)u
)p
left join #chalk c on p.sidewalkid=c.sidewalkid
   and( 
     c.pstart<=p.point and c.pend>p.point
	 or 
	 c.pstart<p.next and c.pend>=p.next
	 )
where next is not null
)
,detailed_path as(
select *
  ,sum(newGr)over(partition by sidewalkid order by point)grN
from(
select * 
  ,case when colors=lag(colors,1,colors)over(partition by sidewalkid order by point) 
     then 0
   else 1 
   end newGr
from(
select sidewalkid,point,next
  ,string_agg(color,'-') within group(order by color) colors
from ranges
group by sidewalkid,point,next
)a
)b
)
,consolidated_path as(
select sidewalkid,min(point)pstart,max(next)pend
  ,min(colors)colors,grn
from detailed_path
group by sidewalkid,grn
)
select sidewalkid,nullif(value,'') color, pstart, pend,colors,	grn	 
from consolidated_path
cross apply string_split(coalesce(colors,''),'-')cl
order by sidewalkid,grn;
;


sidewalkid
color
pstart
pend
colors
grn


1
blue
0
15
blue-red
0

1
red
0
15
blue-red
0

1
null
15
20
null
1

1
blue
20
25
blue
2

1
null
25
30
null
3

1
blue
30
35
blue-red
4

1
red
30
35
blue-red
4

1
blue
35
40
blue
5

2
blue
0
10
blue
0

2
green
10
20
green-red
1

2
red
10
20
green-red
1

2
green
20
25
green-orange
2

2
orange
20
25
green-orange
2

2
yellow
25
35
yellow
3


Warning: Null value is eliminated by an aggregate or other SET operation.


with ranges as(
select p.sidewalkid,p.point,p.next,c.pstart,c.pend,c.color
from(
  select *
  ,lead(point)over(partition by sidewalkid order by point) next
from(
select sidewalkid,pstart point from #chalk
union 
select sidewalkid,pend point from #chalk
)u
)p
left join #chalk c on p.sidewalkid=c.sidewalkid
   and( 
     c.pstart<=p.point and c.pend>p.point
	 or 
	 c.pstart<p.next and c.pend>=p.next
	 )
where next is not null
)
,detailed_path as(
select *
  ,sum(newGr)over(partition by sidewalkid order by point)grN
from(
select * 
  ,case when colors=lag(colors,1,colors)over(partition by sidewalkid order by point) 
     then 0
   else 1 
   end newGr
from(
select sidewalkid,point,next
  ,string_agg(color,'-') within group(order by color) colors
from ranges
group by sidewalkid,point,next
)a
)b
)
select * from detailed_path;


sidewalkid
point
next
colors
newGr
grN


1
0
5
blue-red
0
0

1
5
10
blue-red
0
0

1
10
15
blue-red
0
0

1
15
20
null
1
1

1
20
25
blue
1
2

1
25
30
null
1
3

1
30
35
blue-red
1
4

1
35
40
blue
1
5

2
0
10
blue
0
0

2
10
20
green-red
1
1

2
20
25
green-orange
1
2

2
25
35
yellow
1
3


with ranges as(
select p.sidewalkid,p.point,p.next,c.pstart,c.pend,c.color
from(
  select *
  ,lead(point)over(partition by sidewalkid order by point) next
from(
select sidewalkid,pstart point from #chalk
union 
select sidewalkid,pend point from #chalk
)u
)p
left join #chalk c on p.sidewalkid=c.sidewalkid
   and( 
     c.pstart<=p.point and c.pend>p.point
	 or 
	 c.pstart<p.next and c.pend>=p.next
	 )
where next is not null
)
select * from ranges;


sidewalkid
point
next
pstart
pend
color


1
0
5
0
5
blue

1
0
5
0
5
red

1
5
10
5
10
blue

1
5
10
5
10
red

1
10
15
10
15
blue

1
10
15
10
15
red

1
15
20
null
null
null

1
20
25
20
25
blue

1
25
30
null
null
null

1
30
35
30
35
blue

1
30
35
30
35
red

1
35
40
35
40
blue

2
0
10
0
10
blue

2
10
20
10
20
red

2
10
20
10
25
green

2
20
25
10
25
green

2
20
25
20
25
orange

2
25
35
25
35
yellow


with ranges as(
select p.sidewalkid,p.point,p.next,c.pstart,c.pend,c.color
from(
  select *
  ,lead(point)over(partition by sidewalkid order by point) next
from(
select sidewalkid,pstart point from #chalk
union 
select sidewalkid,pend point from #chalk
)u
)p
left join #chalk c on p.sidewalkid=c.sidewalkid
   and( 
     c.pstart<=p.point and c.pend>p.point
	 or 
	 c.pstart<p.next and c.pend>=p.next
	 )
where next is not null
)
,detailed_path as(
select *
  ,sum(newGr)over(partition by sidewalkid order by point)grN
from(
select * 
  ,case when colors=lag(colors,1,colors)over(partition by sidewalkid order by point) 
     then 0
   else 1 
   end newGr
from(
select sidewalkid,point,next
  ,string_agg(color,'-') within group(order by color) colors
from ranges
group by sidewalkid,point,next
)a
)b
)
select sidewalkid,min(point)pstart,max(next)pend
  ,min(colors)colors,grn
from detailed_path
group by sidewalkid,grn
order by sidewalkid,grn;
;


sidewalkid
pstart
pend
colors
grn


1
0
15
blue-red
0

1
15
20
null
1

1
20
25
blue
2

1
25
30
null
3

1
30
35
blue-red
4

1
35
40
blue
5

2
0
10
blue
0

2
10
20
green-red
1

2
20
25
green-orange
2

2
25
35
yellow
3


Warning: Null value is eliminated by an aggregate or other SET operation.
sidewalkId	pstart	pend	color
1	0	5	blue
1	5	10	blue
1	10	15	blue
1	20	25	blue
1	30	35	blue
1	35	40	blue
1	0	5	red
1	5	10	red
1	10	15	red
1	30	35	red
2	0	10	blue
2	10	20	red
2	10	25	green
2	20	25	orange
2	25	35	yellow
sidewalkid	color	pstart	pend	colors	grn
1	blue	0	15	blue-red	0
1	red	0	15	blue-red	0
1	null	15	20	null	1
1	blue	20	25	blue	2
1	null	25	30	null	3
1	blue	30	35	blue-red	4
1	red	30	35	blue-red	4
1	blue	35	40	blue	5
2	blue	0	10	blue	0
2	green	10	20	green-red	1
2	red	10	20	green-red	1
2	green	20	25	green-orange	2
2	orange	20	25	green-orange	2
2	yellow	25	35	yellow	3