declare @t as table(
group_id int NOT NULL,
txt varchar(max) NOT NULL,
id int NOT NULL
)
insert into @t select 0, 'Hello', 0 union select 0, ' Wor', 1 union select 0, 'ld', 2 union select 1, 'another message', 3
--insert into @t select 0, 'Apple', 0 union select 0, 'Banana', 1 union select 0, 'Grape', 2 union select 1, 'Orange', 3
;with base_cte as(
select
t.txt as value,
t.id,
(
select top 1 id from(
(
select top 1 t2.id from @t t2
where t2.group_id = t.group_id AND t2.id > t.id
order by t2.id asc
)
union all
(select -1)
)tbl
order by id desc
) as nex_id,
t.group_id,
case when EXISTS(
select top 1 t2.id from @t t2
where t2.group_id = t.group_id AND t2.id < t.id
order by t2.id asc
) then 0 else 1 end as is_terminal_value,
(select COUNT(distinct id) from @t t2 where t2.group_id = t.group_id) as record_count_by_group,
1 as terminator
from @t t
)
, recurssive_cte as(
select
value as value_group_start,
id,
group_id,
record_count_by_group,
terminator,
row_number() over(order by group_id, id, terminator) as row_num
from base_cte cte
union all
select
cur.value_group_start + (select nex.value from base_cte nex
where nex.group_id = cur.group_id and
nex.id = cur.row_num
) as value_group_start,
id + 1,
group_id,
record_count_by_group,
terminator + 1,
row_num + 1 as row_num
from recurssive_cte as cur
where
group_id = (select group_id from base_cte nex
where nex.group_id = cur.group_id and
nex.id = cur.row_num
)
and row_num <= record_count_by_group
)
, grouped_cte as (
select max(value_group_start) as items
from recurssive_cte
where terminator = record_count_by_group
group by group_id
)
--select * from base_cte
--select * from recurssive_cte
select * from grouped_cte