declare @Sum int = 20;

declare @Table table(id int, value int);
insert into @Table values (1, 1), (2, 2), (3, 3), (4, 4), (5, 5), (6, 6), (7, 7);

id
3
4
6
7

@Sum

value

declare @Table table(id int, value int);
insert into @Table values (1, 1), (2, 2), (3, 3), (4, 4), (5, 5), (6, 6), (7, 7);

declare @Sum int = 20;

;with cte as (
	select
		id
		,value 
		,S		=value 
		,x		=cast('<a id="'+cast(id as varchar)+'" value="'+cast(value as varchar)+'" />' as varchar(max))
		,lvl	=1	 
	from @Table
	where value<=@Sum
	
	union all
	
	select
		t.id 
		,t.value  
		,S		=t.value+cte.S   
		,x		=cte.x + '<a id="'+cast(t.id as varchar)+'" value="'+cast(t.value as varchar)+'" />'   
		,lvl	=cte.lvl+1	 
	from @Table t inner join cte on t.id>cte.id  
	where t.value+cte.S<=@Sum)
	
select top 1 
	x	=cast(x as xml) 
	,S 
from cte 	
order by @Sum-S, lvl 

declare @Table table(id int, value int);
insert into @Table values (1, 1), (2, 2), (3, 3), (4, 4), (5, 5), (6, 6), (7, 7);

declare @Sum int = 20;

declare @Result table (id int, S int, x varchar(max), lvl int)

insert into @Result
select 
	id
	,value
	,'<a id="'+cast(id as varchar)+'" value="'+cast(value as varchar)+'" />'
	,1
from @Table
where value<=@Sum

--
while not exists(select 1 from @Result where S=@Sum)
begin
	insert into @Result
	select
		t.id 
		,t.value+r.S   
		,r.x + '<a id="'+cast(t.id as varchar)+'" value="'+cast(t.value as varchar)+'" />'   
		,r.lvl+1	 
	from @Table t inner join @Result r on t.id>r.id  
	where t.value+r.S<=@Sum

	if @@rowcount=0 break
		
end	 

select top 1 
	x	=cast(x as xml) 
	,S 
from @Result  	
order by @Sum-S, lvl