Блог

Мне нужно создать разделенный запятыми список значений для каждого идентификатора группы в таблице и заполнить все пропущенные значения, но я не совсем уверен, как поступить. Я использую Server 2016.

моя таблица:

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

ожидаемый результат:

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

Вот альтернативный способ. Создайте функцию, которая принимает начальные и конечные номера и генерирует недостающие номера.

 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
  

Как только это будет сделано, напишите этот SQL:

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

Результат

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

Я думаю, что рекурсивный CTE — хороший подход:

 with t as (
      select v.*
      from (values (1, 1, 15),
                   (2, 1, 16),
                   (3, 1, 20),
                   (4, 2, 12),
                   (5, 2, 15),
                   (6, 3, 8)
           ) v(  id, group_id, value )
     ),
     cte as (
      select t.group_id, min(t.value) as value, max(t.value) as maxvalue, convert(varchar(max), min(t.value)) as vals, 1 as lev
      from t
      group by t.group_id
      union all
      select cte.group_id, value   1, maxvalue, concat(vals, ',', value   1), lev   2
      from cte
      where value < maxvalue
     )
select cte.group_id, cte.vals
from (select cte.*, max(cte.lev) over (partition by cte.group_id) as maxlev
      from cte
     ) cte
where lev = maxlev
order by group_id;
  

Здесь есть db<>fiddle .