T-SQL recursively find all possible combinations

[Psy-Blade]

Hi all,

Been trying to write a bit of code today to take a list of amounts and calculate any possible sum of amounts. I've got so far but unfortunately it gives me duplicate amounts for the same amounts summed in a different order... ie:

1 + 2 + 3 = 6
1 + 3 + 2 = 6
3 + 1 + 2 = 6
...

And so on. I can't for the life of me figure out how to only return a single version of this 'permutaion' (not sure that's the right word?)

The code I'm using at the moment is attached below.

If anyone gets a moment to look this over and suggest anything it'd be much appreciated.

Thanks in advance <3

DROP TABLE #perms, #amounts

;WITH Names(Name) As
(
SELECT	CAST(ref_id AS VARCHAR(MAX))
FROM	worldpay.[dbo].[tbl_cc_refunds]
WHERE	processed IS NULL
), R(Name,Lvl) AS
(
SELECT CAST(',' + Name AS VARCHAR(MAX)), 1
FROM Names
UNION ALL
SELECT R.Name + ',' + N.Name, Lvl + 1
FROM R JOIN Names N ON R.Name + ',' NOT LIKE '%,' + N.Name + ',%'
)
SELECT STUFF(Name,1,1,'') AS Name
INTO #perms
FROM R
ORDER BY Lvl, Name

DECLARE @id AS nvarchar(max)
DECLARE @sql AS nvarchar(max)

CREATE TABLE #amounts 
(
	ref_id nvarchar(max),
	amount money
)

DECLARE cur CURSOR
FOR 
	SELECT	Name
	FROM	#perms

OPEN cur

FETCH NEXT FROM cur
INTO @id

WHILE @@FETCH_STATUS = 0
BEGIN

	SET @sql = 'INSERT INTO #amounts (ref_id, amount)
				SELECT	''' + @id + ''', SUM(CAST(amount AS money))
				FROM	worldpay.[dbo].[tbl_cc_refunds]
				WHERE	ref_id IN (' + @id + ')'

	print @sql
	exec (@sql)

	FETCH NEXT FROM cur
	INTO @id
END

SELECT  FROM #amounts
order by amount

 

[sist_si]

Depending on the version of sql server, I'd use CTE (common table expressions) for recursion. They're really handy for this kind of thing:

http://msdn.microsoft.com/en-us/library/ms186243(v=sql.105).aspx