Group Item by Sum of a column

Question

I have a doubt in SQL Server query

I have a table dbo.[Files] with columns Id, FileName, FileSize(MB)

Id   FileName   FileSize(MB)
----------------------------
1    aa.gif        3 
2    bb.gif        5 
3    cc.gif        7 
4    dd.gif        5 
5    ee.gif        2 
6    fff.gif       4 
7    gg.gif        3 

My requirement is, I have to group with maximum file size limit and give a group Number for a group having sum of filesize less than or equal to the maximum limit

Suppose maximum file size sum is 10

Example result:

Id   FileName   FileSize(MB)   GroupNo
--------------------------------------
1    aa.gif         3             1 
2    bb.gif         5             1 
5    ee.gif         2             1 
3    cc.gif         7             2 
7    gg.gif         3             2 
4    dd.gif         5             3 
6    fff.gif        4             3 

Sum of FileSize in a Group is 10

Final result required

GroupNo  Ids
--------------
1        1,2,5 
2        4,6 
3        3,7 

Answer 1

I tried quite bit, but in the end I had to settle for a procedural solution, albeit in T-SQL. The following script works on a temporary table @t1 (columns: id, fn, size, gr) into which the data has to be copied first. Alternatively you can also equip your original table with an additional group-id column gr and then work on that table.

declare @g int=1, @cnt int,@si int;
    -- group-no, row-count, size of group
select @cnt=count(*) from #t1 where gr=0;
while (@cnt>0) begin
select @si=isnull(sum(size),0) from #t1 where gr=@g;
update #t1 set gr=@g where id = (
 select top 1 id from #t1
 where gr=0 and @si+size<=10 
 order by @si+size desc);
 if (@@rowcount=0) begin
  if (@si=0) update top(1) #t1 set gr=@g 
             where gr=0; -- for files > 10mb
    set @g=@g+1;
  end;
  select @cnt=count(*) from #t1 where gr=0
end;

select * from #t1

A working example can be found at https://data.stackexchange.com/stackoverflow/query/337062/group-into-10mb-chunks-2

Answer 2

Can you please check if the below query is fetching your desired output :

        Select 
        GroupNo,
        LISTAGG((id)|| ',' ORDER BY id)
        from your_table;