How to make sum of rows value until sum value is 1.0 in SQL Server

I am stuck with this SQL Server query; here is my set of records:

Name    Value
----    ----
abc      0.7
xyz      0.4
pqr      0.6
ijk      0.3
fgh      0.1
cde      1.0
uvw      0.8

I want a query which will give me sum of value until it reach 1.0 and greater then continue same with next rows, like

Names         Value
-----         -----
abc,xyz       1.1
pqr,ijk,fgh   1.0
cde           1.0
uvw           0.8

Please help me with this query; I tried with partitions and recursive CTE, but nothing worked so far.

Answer

SQL tables represent unordered sets. Your results assume a particular ordering and there is no column for the ordering. The following assumes that such a column exists. It is just called id.

This type of problem requires a recursive CTE (or some other iterative processing), as far as I know. Here is one method:

with t as (
      select t.*, row_number() over (order by id) as seqnum
      from mytable t
     ),
     cte as (
      select top (1) seqnum, convert(varchar(max), name) as name, convert(decimal(10, 1), value) as value,
             (case when value >= 1.0 then 1 else 0 end) as is_new
      from t
      order by seqnum
      union all
      select t.seqnum, 
             (case when cte.value >= 1 then t.name else concat(cte.name, ',', t.name) end),
             convert(decimal(10, 1), (case when cte.value > =1 then t.value else t.value + cte.value end)),
             (case when cte.value >= 1 then 1 else 0 end) as is_new
      from cte join
           t
           on t.seqnum = cte.seqnum + 1
     )
select name, value as sum_value
from (select cte.*, lead(name) over (order by seqnum) as nextname
      from cte
     ) cte
where nextname is null or nextname not like name + '%';

Here is a db<>fiddle.