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