ck9663 (8/6/2013)
How about this?To change the number of batch, change the value of @batch variable.
declare @batch as smallint
set @batch = 6
if object_id('tempdb..#sample') is not null
drop table #sample
SELECT top 20
abs(id) as number
into #sample
FROMmaster.dbo.sysobjects
select * from #sample
;with batched
as
(
select
batch_id = (row_number() over(order by number) - 1)/@batch,
number
from #sample
), sorted
as
(
select
batch_id,
sort_id = row_number() over(partition by batch_id order by number),
number
from batched
),GatherAll
as
(
select batch_id, sort_id, cast(number as varchar(max)) as Gathered
from sorted
where sort_id = 1
union all
select s.batch_id, s.sort_id, cast(rtrim(Gathered) + ',' + cast(s.number as varchar(50)) as varchar(max))
from sorted s
inner join GatherAll g on g.batch_id = s.batch_id and s.sort_id = g.sort_id + 1
)
select batch_id, max(Gathered)
from GatherAll
group by Batch_Id
Happy Coding!!!
~~ CK
Please see the following article for why you might not want to use a recursive CTE to count.
[font="Arial Black"]Hidden RBAR: Counting with Recursive CTE's[/font]
[/url]
--Jeff Moden
Change is inevitable... Change for the better is not.