By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table #chalk (
sidewalkId int,
start int,
"end" int,
color varchar(50)
);
insert into #chalk values
(1, 0, 15, 'blue'),
(1, 20, 25, 'blue'),
(1, 30, 40, 'blue'),
(1, 0, 15, 'red'),
(1, 30, 35, 'red');
with
-- Get all cross points (where lines start or end).
-- union (without all) will take care of applying a "distinct".
crosses as (select sidewalkId, start pos from #chalk union select sidewalkId, "end" from #chalk),
ranges0 as (select sidewalkId, pos start, lead(pos) over (partition by sidewalkId order by pos) "end" from crosses),
ranges as (select * from ranges0 where "end" is not null),
segments as
(
select r.sidewalkId, r.start, r."end", l.color
from ranges r
-- left join to have gaps in the results.
left join #chalk l on l.sidewalkId = l.sidewalkId and l.start < r."end" and l."end" > r.start
),
band as
(
select
sidewalkId, start, "end",
string_agg(color, ',') within group (order by color) name
from segments
group by sidewalkId, start, "end"
),
cte as (
select sidewalkId, name
| sidewalkId | color | start | end | rna_start | rna_end | bandname |
|---|---|---|---|---|---|---|
| 1 | blue | 0 | 15 | 1 | 1 | blue,red |
| 1 | red | 0 | 15 | 1 | 1 | blue,red |
| 1 | null | 15 | 20 | 2 | 2 | null |
| 1 | blue | 20 | 25 | 3 | 3 | blue |
| 1 | null | 25 | 30 | 4 | 4 | null |
| 1 | red | 30 | 35 | 5 | 5 | blue,red |
| 1 | blue | 30 | 35 | 5 | 5 | blue,red |
| 1 | blue | 35 | 40 | 6 | 6 | blue |