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:
x
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'
Advertisement
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