tsql - Collapse data ranges and overlapping data in SQL Server 2014 -

i have table ranges in below:

id  actioncode  group1  type    low         high 33            840     mm      000295800   000295899 34            840     mm      000295900   000295999 

i need collapse 2 rows consecutive data 1 row instance above

actioncode  group1  type    low         high           840     mm      000295800   000295999    

for actioncode, group1, type...

there can overlapping data ranges, preceeding zeros, etc.

sample table:

if object_id('tempdb..#testtable') not null     drop table #testtable  create table #testtable(     [id] [int] identity(1,1) not null,     [actioncode] [char](1) not null,     [group1] [varchar](50) not null,     [type] [varchar](2) null,     [low] [varchar](50) not null,     [high] [varchar](50) not null,     constraint [pk_#testtable] primary key clustered ([id] asc)   )   go  insert #testtable (actioncode, group1, type, low, high) values ('a','840','jj','401299870','401299879') insert #testtable (actioncode, group1, type, low, high) values ('a','840','aa','401644000','401646999') insert #testtable (actioncode, group1, type, low, high) values ('a','840','jj','401378000','401378999') insert #testtable (actioncode, group1, type, low, high) values ('a','840','jj','401644000','401646999') insert #testtable (actioncode, group1, type, low, high) values ('a','840','jj','401299970','401299979') insert #testtable (actioncode, group1, type, low, high) values ('a','840','jj','400424000','400424999') insert #testtable (actioncode, group1, type, low, high) values ('a','840','jj','401299990','401299996') -- ds insert #testtable (actioncode, group1, type, low, high) values ('d','840','jj','401198000','401198999') insert #testtable (actioncode, group1, type, low, high) values ('d','840','jj','401649000','401649999') insert #testtable (actioncode, group1, type, low, high) values ('a','840','jj','401299997','401299997') insert #testtable (actioncode, group1, type, low, high) values ('d','840','jj','401376000','401390999') insert #testtable (actioncode, group1, type, low, high) values ('a','840','jj','401655000','401668999') insert #testtable (actioncode, group1, type, low, high) values ('a','840','jj','400411000','400411999') insert #testtable (actioncode, group1, type, low, high) values ('d','840','jj','400414000','400414999') insert #testtable (actioncode, group1, type, low, high) values ('a','840','jj','401646000','401646999') insert #testtable (actioncode, group1, type, low, high) values ('d','840','jj','400413000','400413999') insert #testtable (actioncode, group1, type, low, high) values ('a','840','jj','401654000','401654999') insert #testtable (actioncode, group1, type, low, high) values ('a','356','gg','522892000','522892999') insert #testtable (actioncode, group1, type, low, high) values ('a','356','gg','522892100','522892199') insert #testtable (actioncode, group1, type, low, high) values ('a','356','gg','522892400','522892999') insert #testtable (actioncode, group1, type, low, high) values ('a','356','aa','522892400','522892999') insert #testtable (actioncode, group1, type, low, high) values ('a','356','aa','522892300','522892399') -- different types overlap range insert #testtable (actioncode, group1, type, low, high) values ('a','356','aa','522892200','522892999') insert #testtable (actioncode, group1, type, low, high) values ('a','356','kk','522892000','522892999') insert #testtable (actioncode, group1, type, low, high) values ('a','356','kk','522892200','522892999') insert #testtable (actioncode, group1, type, low, high) values ('a','356','kk','522892300','522892399') insert #testtable (actioncode, group1, type, low, high) values ('a','356','kk','522892400','522892999') insert #testtable (actioncode, group1, type, low, high) values ('a','356','kk','522892100','522892199') insert #testtable (actioncode, group1, type, low, high) values ('a','356','gg','522892200','522892999') insert #testtable (actioncode, group1, type, low, high) values ('a','356','gg','522892300','522892399') insert #testtable (actioncode, group1, type, low, high) values ('a','356','aa','522892100','522892199') insert #testtable (actioncode, group1, type, low, high) values ('a','356','aa','522892000','522892999') -- leading zeros insert #testtable (actioncode, group1, type, low, high) values ('a','840','mm','000295800','000295899') insert #testtable (actioncode, group1, type, low, high) values ('a','840','mm','000295900','000295999') -- overlap insert #testtable (actioncode, group1, type, low, high) values ('a','840','nn','623295800','623295999') insert #testtable (actioncode, group1, type, low, high) values ('a','840','nn','623295900','623295999') insert #testtable (actioncode, group1, type, low, high) values ('a','840','nn','623295900','623296099') insert #testtable (actioncode, group1, type, low, high) values ('a','840','nn','623296100','623296299') go   select * #testtable order low 

i can small table using recursive cte, table has little less million rows. once on size takes long time run. there index on "grouped" columns.

there must way quickly, i'm hitting roadblocks.

^

i think looking this:

with cte (     select  actioncode,             group1,             [type],             low,             high,             next_low =  lead(low,1) on (partition actioncode,group1,[type] order id),             next_high = lead(high,1) on (partition actioncode,group1,[type] order id)     #testtable )  select  actioncode,         group1,         [type],         low,         high cte       low != next_low          , high!= next_high