SELECT *
FROM   (SELECT *
        FROM   (SELECT 0 AS col1
                UNION ALL
                SELECT 10 AS col1) a
               CROSS JOIN (SELECT 0 AS col2
                           UNION ALL
                           SELECT 7 AS col2) b
               CROSS JOIN (SELECT 0 AS col3
                           UNION ALL
                           SELECT 7 AS col3) c
               CROSS JOIN (SELECT 0 AS col4
                           UNION ALL
                           SELECT 6 AS col4) d
               CROSS JOIN (SELECT 0 AS col5
                           UNION ALL
                           SELECT 4 AS col5) e
               CROSS JOIN (SELECT 0 AS col6
                           UNION ALL
                           SELECT 2 AS col6) f) x
WHERE  col1 + col2 + col3 + col4 + col5 + col6 = 20  

DECLARE	@Sample TABLE
	(
		PieceID INT IDENTITY(1, 1) PRIMARY KEY CLUSTERED,
		PieceLength INT NOT NULL
	)

INSERT	@Sample
	(
		PieceLength
	)
SELECT	10 UNION ALL
SELECT	7 UNION ALL
SELECT	7 UNION ALL
SELECT	6 UNION ALL
SELECT	4 UNION ALL
SELECT	2

DECLARE	@WantedLength INT

SET	@WantedLength = 20

;WITH ctePermutations(PieceGroup, PieceLength, PiecePath, PieceTotal)
AS (
	SELECT	PieceID AS PieceGroup,
		PieceLength,
		'<p>' + CAST(PieceID AS VARCHAR(MAX)) + '</p>' AS PiecePath,
		PieceLength AS PieceTotal
	FROM	@Sample

	UNION ALL

	SELECT		p.PieceGroup,
			s.PieceLength,
			p.PiecePath + '<p>' + CAST(s.PieceID AS VARCHAR(MAX)) + '</p>' AS PiecePath,
			p.PieceTotal + s.PieceLength AS PieceTotal
	FROM		@Sample AS s
	INNER JOIN	ctePermutations AS p ON p.PiecePath NOT LIKE '%<p>' + CAST(s.PieceID AS VARCHAR(MAX)) + '</p>%'
	WHERE		s.PieceLength >= p.PieceLength
), cteResult(PieceGroup, PieceID)
AS (
	SELECT DISTINCT	x.PieceGroup,
			f.a.value('.', 'INT') AS PieceID
	FROM		(
				SELECT		PieceGroup,
						CAST(PiecePath AS XML) AS w
				FROM		ctePermutations
				WHERE		PieceTotal = @WantedLength
			) AS x
	CROSS APPLY	x.w.nodes('p') AS f(a)
)
SELECT		DENSE_RANK() OVER (ORDER BY r.PieceGroup) AS Alternative,
		s.PieceLength
FROM		cteResult AS r
INNER JOIN	@Sample AS s ON s.PieceID = r.PieceID
ORDER BY	r.PieceGroup,
		s.PieceLength

quote:

---

Originally posted by Peso

Dynamic SQL? Why?

---

Alternative	PieceLength
1	4
1	7
1	7
1	10

DECLARE	@Sample TABLE
	(
		PieceID INT IDENTITY(1, 1) PRIMARY KEY CLUSTERED,
		PieceLength INT NOT NULL
	)

INSERT	@Sample
	(
		PieceLength
	)
SELECT	10 UNION ALL
SELECT	7 UNION ALL
SELECT	7 UNION ALL
SELECT	6 UNION ALL
SELECT	4 UNION ALL
SELECT	2

DECLARE	@WantedLength INT

SET	@WantedLength = 20

;WITH ctePermutations(PieceLength, PiecePath, PieceTotal)
AS (
	SELECT	PieceLength,
		'<p>' + CAST(PieceID AS VARCHAR(MAX)) + '</p>' AS PiecePath,
		PieceLength AS PieceTotal
	FROM	@Sample

	UNION ALL

	SELECT		s.PieceLength,
			p.PiecePath + '<p>' + CAST(s.PieceID AS VARCHAR(MAX)) + '</p>' AS PiecePath,
			p.PieceTotal + s.PieceLength AS PieceTotal
	FROM		@Sample AS s
	INNER JOIN	ctePermutations AS p ON p.PiecePath NOT LIKE '%<p>' + CAST(s.PieceID AS VARCHAR(MAX)) + '</p>%'
	WHERE		s.PieceLength >= p.PieceLength
), cteIntermediate(PiecePath, PieceLength, RecID)
AS (
	SELECT 		x.PiecePath,
			s.PieceLength,
			RANK() OVER (ORDER BY x.PieceTotal DESC) AS RecID
	FROM		(
				SELECT	PiecePath,
					CAST(PiecePath AS XML) AS w,
					PieceTotal
				FROM	ctePermutations
				WHERE	PieceTotal <= @WantedLength
			) AS x
	CROSS APPLY	x.w.nodes('p') AS f(a)
	INNER JOIN	@Sample AS s ON s.PieceID = f.a.value('.', 'INT')
), cteResult(RecID, t)
AS (
	SELECT		ROW_NUMBER() OVER (PARTITION BY f.t ORDER BY i.PiecePath) AS RecID,
			f.t
	FROM		cteIntermediate AS i
	CROSS APPLY	(
				SELECT		CAST('<i>' + CAST(e.PieceLength AS VARCHAR(MAX)) + '</i>' AS XML)
				FROM		cteIntermediate AS e
				WHERE		e.PiecePath = i.PiecePath
						AND e.RecID = 1
				ORDER BY	e.PieceLength
				FOR XML		PATH('')
			) AS f(t)
	WHERE		i.RecID = 1
)
SELECT		r.Alternative,
		f.a.value('.', 'INT') AS PieceLength,
		SUM(f.a.value('.', 'INT')) OVER (PARTITION BY r.Alternative) AS Total
FROM		(
			SELECT	ROW_NUMBER() OVER (ORDER BY t) AS Alternative,
				CAST(t AS XML) AS w
			FROM	cteResult
			WHERE	RecID = 1
		) AS r
CROSS APPLY	r.w.nodes('i') AS f(a)

quote:

---

Originally posted by notmyrealname

Once i have a list of all possible combinations i can then select the combination that is the closest to the desired length.

---

quote:

---

Originally posted by Peso

Yes? It gives you 4 as requested.

---

-- Initialize the search parameter
DECLARE	@WantedValue INT = 19

-- Stage the source data
DECLARE	@Data TABLE
	(
		RecID INT IDENTITY(1, 1) PRIMARY KEY CLUSTERED,
		MaxItems INT,
		CurrentItems INT DEFAULT 0,
		FaceValue INT,
		BestUnder INT DEFAULT 0,
		BestOver INT DEFAULT 1
	)

-- Aggregate the source data
INSERT		@Data
		(
			MaxItems,
			FaceValue
		)
SELECT		COUNT(*),
		Qty
FROM		(
			VALUES	(10),
				(7),
				(7),
				(6),
				(4),
				(2)
		) AS d(Qty)
GROUP BY	Qty
ORDER BY	Qty DESC

-- Declare some control variables
DECLARE	@CurrentSum INT,
	@BestUnder INT,
	@BestOver INT,
	@RecID INT
 
-- If exact single wanted sum, select that item!
IF EXISTS (SELECT * FROM @Data WHERE FaceValue = @WantedValue)
	BEGIN
		SELECT	0 AS SumType,
			1 AS Items,
			FaceValue
		FROM	@Data
		WHERE	FaceValue = @WantedValue

		RETURN
       END

-- If productsum is less to the wanted sum, select all items!
IF (SELECT SUM(MaxItems * FaceValue) FROM @Data) < @WantedValue
	BEGIN
		SELECT	-1 AS SumType,
			MaxItems AS Items,
			FaceValue
		FROM	@Data
 
		RETURN
       END

-- If productsum is equal to the wanted sum, select all items!
IF (SELECT SUM(MaxItems * FaceValue) FROM @Data) = @WantedValue
	BEGIN
		SELECT	0 AS SumType,
			MaxItems AS Items,
			FaceValue
		FROM	@Data

		RETURN
       END
 
-- Delete all unworkable FaceValues, keep one greater FaceValue because of oversum. 
DELETE
FROM	@Data
WHERE	FaceValue > (SELECT MIN(FaceValue) FROM @Data WHERE FaceValue >= @WantedValue)
 
-- Update MaxItems to a proper value
UPDATE	@Data
SET	MaxItems =	CASE
				WHEN 1 + (@WantedValue - 1) / FaceValue < MaxItems THEN 1 + (@WantedValue - 1) / FaceValue
				ELSE MaxItems
			END
 
-- Update BestOver to a proper value
UPDATE	@Data
SET	BestOver = MaxItems
 
-- Initialize the control mechanism
SELECT	@RecID = MIN(RecID),
	@BestUnder = 0,
	@BestOver = SUM(BestOver * FaceValue)
FROM	@Data
 
-- Do the loop!
WHILE @RecID IS NOT NULL
	BEGIN
		-- Reset all "bits" not incremented
		UPDATE	@Data
		SET	CurrentItems = 0
		WHERE	RecID < @RecID
 
		-- Increment the current "bit"
		UPDATE	@Data
		SET	CurrentItems = CurrentItems + 1
		WHERE	RecID = @RecID
 
		-- Get the current sum
		SELECT	@CurrentSum = SUM(CurrentItems * FaceValue)
		FROM	@Data
		WHERE	CurrentItems > 0
 
		-- Stop here if the current sum is equal to the sum we want
		IF @CurrentSum = @WantedValue
			BREAK
		ELSE
			-- Update the current BestUnder if previous BestUnder is less
			IF @CurrentSum > @BestUnder AND @CurrentSum < @WantedValue
				BEGIN
					UPDATE	@Data
					SET	BestUnder = CurrentItems
 
					SET	@BestUnder = @CurrentSum
				END
			ELSE
				-- Update the current BestOver if previous BestOver is more
				IF @CurrentSum > @WantedValue AND @CurrentSum < @BestOver
					BEGIN
						UPDATE	@Data
						SET	BestOver = CurrentItems
 
						SET	@BestOver = @CurrentSum
					END
 
		-- Find the next proper "bit" to increment
		SELECT	@RecID = MIN(RecID)
		FROM	@Data
		WHERE	CurrentItems < MaxItems
	END
 
-- Now we have to investigate which type of sum to return
IF @RecID IS NULL
	IF @WantedValue - @BestUnder < @BestOver - @WantedValue
		-- If BestUnder is closer to the sum we want, choose that
		SELECT	-1 AS SumType,
			BestUnder AS Items,
			FaceValue
		FROM	@Data
		WHERE	BestUnder > 0
	ELSE
		-- If BestOver is closer to the sum we want, choose that
		SELECT	1 AS SumType,
			BestOver AS Items,
			FaceValue
		FROM	@Data
		WHERE	BestOver > 0
ELSE
	-- We have an exact match
	SELECT	0 AS SumType,
		CurrentItems AS Items,
		FaceValue
	FROM	@Data
	WHERE	CurrentItems > 0