Group Column in Different Rows Into A Single Column

I encountered a question from the forum that I frequented on, on how to group column from different rows into a single column as a comma delimited value. The number of rows that will be grouped will be variable.

Say this is the input table:

if object_id('tempdb..#sample') is not null 
   drop table #sample

SELECT top 20 
   abs(id) as number
   into #sample
FROM master.dbo.sysobjects

select * from #sample

This is how the table look like

number
-----------
4
5
7
8
15
25
28
29
34
41
42
45
46
47
49

I intentionally did not use sequential numbers so this snippet can be used to concatenate numbers or string. The table may or may not be sorted, as well.

The requirement is to grouped these values depending on user input.

If the user wants to group this by two, the result should look like this:

grouped
--------
4,5
7,8
15,25
28,29
34,41
42,45
46,47
49

If the user wants to group this by five, the result should look like this:

grouped
----------------
4,5,7,8,15
25,28,29,34,41
42,45,46,47,49

Here's the query to build the delimited string:

declare @batch as smallint

set @batch = 5

;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 grouped = max(Gathered)
from GatherAll
group by Batch_Id

Change the value of the variable @batch to change the number of rows to be grouped.

As always, comments are welcome...


~~ CK