SQL Server 2012

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE Items (
  ID INT primary key identity(1,1),
  Name VARCHAR(100) not null, 
  Price decimal(10,2) not null
);

insert into Items 
 (Name, Price) values 
 ('jacket',40)
,('pants', 30)
,('tshirt',20)
,('socks', 10)
;
4 rows affected

CREATE TABLE Nums (n INT primary key);
WITH DIGITS (n) AS 
(
    SELECT n 
    FROM 
    (VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) v(n)
)
INSERT INTO Nums (n)
SELECT (tens.n * 10) + single.n 
FROM DIGITS single
CROSS JOIN DIGITS tens;
100 rows affected

declare @PriceLimit decimal(10,2) = 100;

WITH COMBOS AS
(
    SELECT 
     i1.id as id1, i2.id as id2, i3.id as id3, 
     n1.n as n1, n2.n as n2, n3.n as n3,
     (n1.n + n2.n + n3.n) AS TotalItems,
     (i1.Price * n1.n + i2.Price * n2.n + i3.Price * n3.n) as TotalCost
    FROM Items i1
    JOIN Items i2 ON i2.id > i1.id AND i2.Price < @PriceLimit
    JOIN Items i3 ON i3.id > i2.id AND i3.Price < @PriceLimit
    JOIN Nums n1 
      ON n1.n between 1 and FLOOR(@PriceLimit/i1.Price)
     AND (i1.Price * n1.n) < @PriceLimit
    JOIN Nums n2 
      ON n2.n between 1 and FLOOR(@PriceLimit/i2.Price)
     AND (i1.Price * n1.n + i2.Price * n2.n) < @PriceLimit
    JOIN Nums n3 
      ON n3.n between 1 and FLOOR(@PriceLimit/i3.Price) 
     AND (i1.Price * n1.n + i2.Price * n2.n + i3.Price * n3.n) <= @PriceLimit
     AND (i1.Price * n1.n + i2.Price * n2.n + i3.Price * (n3.n+1)) > @PriceLimit
    WHERE i1.Price < @PriceLimit
)
SELECT 
 c.TotalItems, c.TotalCost,
 CONCAT (c.n1,' ',item1.Name,', ',c.n2,' ',item2.Name,', ',c.n3,' ',item3.Name) AS ItemList
FROM COMBOS c
LEFT JOIN Items item1 ON item1.id = c.id1
LEFT JOIN Items item2 ON item2.id = c.id2
LEFT JOIN Items item3 ON item3.id = c.id3
ORDER BY c.TotalCost desc, c.TotalItems desc, c.id1, c.id2, c.id3;


TotalItems
TotalCost
ItemList


7
100.00
1 pants, 1 tshirt, 5 socks

6
100.00
1 jacket, 1 tshirt, 4 socks

6
100.00
1 pants, 2 tshirt, 3 socks

5
100.00
1 jacket, 1 pants, 3 socks

5
100.00
1 jacket, 2 tshirt, 2 socks

5
100.00
1 pants, 3 tshirt, 1 socks

5
100.00
2 pants, 1 tshirt, 2 socks

3
90.00
1 jacket, 1 pants, 1 tshirt

TotalItems	TotalCost	ItemList
7	100.00	1 pants, 1 tshirt, 5 socks
6	100.00	1 jacket, 1 tshirt, 4 socks
6	100.00	1 pants, 2 tshirt, 3 socks
5	100.00	1 jacket, 1 pants, 3 socks
5	100.00	1 jacket, 2 tshirt, 2 socks
5	100.00	1 pants, 3 tshirt, 1 socks
5	100.00	2 pants, 1 tshirt, 2 socks
3	90.00	1 jacket, 1 pants, 1 tshirt