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, 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');
with
band as
(
select
sidewalkId, start, "end",
string_agg(color, ',') within group (order by color) name
from #chalk
group by sidewalkId, start, "end"
),
cte as (
select sidewalkId, name
, row_number() over (partition by sidewalkId order by sidewalkId, start) rna
, row_number() over (partition by sidewalkId, name order by sidewalkId, start) rnc
, start, "end"
from band
)
| sidewalkId | color | start | end | rna_start | rna_end | bandname |
|---|---|---|---|---|---|---|
| 1 | blue | 0 | 15 | 1 | 3 | blue,red |
| 1 | red | 0 | 15 | 1 | 3 | blue,red |
| 1 | blue | 20 | 25 | 4 | 4 | blue |
| 1 | blue | 30 | 35 | 5 | 5 | blue,red |
| 1 | red | 30 | 35 | 5 | 5 | blue,red |
| 1 | blue | 35 | 40 | 6 | 6 | blue |