What is the best to generate rows based on division and remainer?
declare @Total int = 203;
declare @Batch int = 100;
I did some complicated query with T-SQL but it is not working.
Result should be:
100
100
3
What is the best to generate rows based on division and remainer?
declare @Total int = 203;
declare @Batch int = 100;
I did some complicated query with T-SQL but it is not working.
Result should be:
100
100
3
2条答案
按热度按时间kqlmhetl1#
Assuming the latest version of SQL Server (2022 at the time of writing), as no indication of version has been given, one method would be to use
GENERATE_SERIES
. You can use this to generate one row per batch, and then return the modulo when the upper value is greater than than the total:uelo1irk2#
If you don't have SQL2022 here's a way of building a recursive CTE and then using that data to build the result set:
It initially builds the CTE in steps of @Batch but as a total (e.g. 100 then 200) up until the next iteration would take you over the total. I added a column to iterate by counts of 1 so that we can then later divide Num by x to get each value back to the Batch size.
Finally in the select there's a UNION to get the remainder which is the total minus the maximum value the CTE iterated to