# divide value to same number of parts using sql server

61 Reputation points
2021-03-09T13:06:09.023+00:00

Hi All, i have requirement in sql server, i need to divide(12) total (100) value as below parts. Total an divide value dynamically changes. ![75795-image.png][1] [1]: /api/attachments/75795-image.png?platform=QnA Thanks in Advance,

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
9,734 questions

1. 8,076 Reputation points
2021-03-09T14:08:05.59+00:00

Try this:

``````DECLARE @dividend int = 70;
DECLARE @divisor int = 12;
SELECT CEILING(@dividend * 1.0 / @divisor);
``````

2. 28,781 Reputation points
2021-03-09T14:16:08.26+00:00

(70/12 = 6)

You must have a strange calculator, with mine I get a different result of 5.83333 . Anyway, this one?

``````DECLARE @total int = 70, @count int = 12;

;WITH cte AS
(SELECT 1 AS RN, @total AS RestVal, @count AS Value
UNION ALL
SELECT RN + 1, RestVal - @count, CASE WHEN RestVal - @count > @count THEN @count ELSE RestVal - @count END
FROM cte
WHERE RestVal > @count)
SELECT RN, Value
FROM cte
``````

Result

3. 94,261 Reputation points
2021-03-09T20:27:58.817+00:00

In addition, check a secondary possible solution, based on a relatively new approach:

``````declare @total int = 70
declare @d int = 11

select row_number() over (order by [value] desc) groups, [value]
from
(
select @d
from string_split(replicate('v', @total / @d - 1), 'v')
union all
select @total % @d where @total % @d <> 0
) d([value])
order by groups
``````

Can be easily adjusted to work with more than 8000 rows.

4. 24,136 Reputation points
2021-03-10T01:56:16.69+00:00

Hi @BHVS ,

Welcome to Microsoft Q&A!

You could refer other experts's answer and check whether they are working.

Please also refer below another method using loop:

``````drop table if exists #temp

DECLARE @total int = 70, @count int = 12;

DECLARE @n int=0;

create table #temp
(ID int,
NUM int);

while @n<=@total/@count
begin
if @total-@count*@n>@count
insert into #temp values (@n+1,@count)
else
insert into #temp values (@n+1,@total-@count*@n)
set @n=@n+1
end

select * from #temp
``````

Output:

``````ID	NUM
1	12
2	12
3	12
4	12
5	12
6	10
``````

Best regards
Melissa