suppose i have a given series like 10,20,17,3,6,8,2,4.18,25= total 113. i need to split in to 3 categories each contain max sum of 50 and and each category max of 5 elements.

declare @series nvarchar(max) = '10,20,17,3,6,8,2,4,18,25'
create table ##t2(val int, cat int)
declare @max int, @catcnt int = 1, @i int = 1, @sum int = 0, @val int, @cntr int = 0
select * into ##t from dbo.split(@series,',') s
select @max = MAX(id) from ##t
while @i <= @max
begin
select @val = val from ##t where id = @i
set @cntr = @cntr + 1
if ( (@val + @sum) > 50 or @cntr = 5)
begin
select @catcnt = @catcnt + 1, @cntr = 0
insert into ##t2 select @val, @catcnt
end
else
begin
insert into ##t2 select @val, @catcnt
end
set @i = @i + 1
end
select * from ##t2
drop table ##t
drop table ##t2

Before you run the above script, you need the create the
SQL Split string function on your database. It returns the splitted value besides the order of the string fragment

Member

12 Points

28 Posts

## In a given series how to get max sum of 5 no and that should be less than 50.

May 09, 2016 08:13 AM|Rammi44|LINK

suppose i have a given series like 10,20,17,3,6,8,2,4.18,25= total 113. i need to split in to 3 categories each contain max sum of 50 and and each category max of 5 elements.

how to do it.

Contributor

5981 Points

1411 Posts

## Re: In a given series how to get max sum of 5 no and that should be less than 50.

May 09, 2016 01:19 PM|eralper|LINK

Hello Rammi44,

Please check following SQL While loop statement

Before you run the above script, you need the create the SQL Split string function on your database. It returns the splitted value besides the order of the string fragment

The result will be as follows

I hope it helps

SQL Server 2016