How to select multiple ranges seperated by group id

I need to create a comma delimited list of values for each group id in a table and fill in any missing values but I’m not quite sure how to proceed. I’m using Server 2016.

my table:

  id  |  group_id  |  value  
------+------------+---------
   1  |         1  |    15
   2  |         1  |    16
   3  |         1  |    20
   4  |         2  |    12
   5  |         2  |    15
   6  |         3  |     8

expected output:

 group_id  |         list
-----------+-----------------------
        1  |  '15,16,17,18,19,20'
        2  |  '12,13,14,15'
        3  |  '8'

Answer

Here’s an alternate way. Create a function that takes start and end numbers and generates missing numbers.

create function dbo.get_list(@start int, @end int)
returns varchar(1000)
as begin
    declare @retval varchar(1000);
    set @retval = cast(@start as varchar(1000));
    set @start = @start + 1;

    while @start <= @end
    begin
        set @retval = @retval + ',' + cast(@start as varchar(1000));
        set @start = @start + 1;
    end;

    return @retval
end

Once that is done, write this SQL:

select group_id, dbo.get_list(min(value), max(value))
from test
group by group_id

Result

group_id  list
--------  ------------------
1         15,16,17,18,19,20
2         12,13,14,15
3         8