SQL Server 2005 - Combinations

[CVMichael]

For one of my projects I needed to find all the combinations between some IDs.
I found code for permutations, and I modified it to get combinations: http://www.sqlservercentral.com/Foru....aspx#bm634104

This function is using XML as a parameter to give it the list of IDs you want to combine. It returns a table as a result.

-- =============================================
-- Author:		<Michael Ciurescu>
-- Create date: <2011-08-25>
-- Description:	<Get all combinations between unique IDs/Numbers>
-- =============================================
CREATE FUNCTION [dbo].[fn_Combinations]
(
	  @Data XML
	, @ReturnSingles BIT = 0
)
RETURNS @ret TABLE 
(
	  Combination		INT
	, Combination_Items	XML
	, [ID]				INT
)
AS
BEGIN
	/*
	DECLARE @XML XML
	
	SET @XML = '
	<Data ID="123" />
	<Data ID="456" />
	<Data ID="789" />
	'
	
	SELECT * FROM dbo.fn_Combinations(@XML, default)
	*/
	
	-- http://www.sqlservercentral.com/Forums/Topic218243-186-2.aspx#bm634104
	
	; WITH tbl AS (
		SELECT POWER(2, ROW_NUMBER() OVER(ORDER BY (SELECT 1)) - 1) AS Marker
			, c.value('@ID', 'VARCHAR(MAX)') AS ID
		FROM @Data.nodes('//Data') T(c)
	)
	, a AS (SELECT COUNT(*) AS TotalRows FROM tbl)
	, b AS (
		SELECT Marker, CAST(1 AS INT) AS Level, '<ID>' + CAST(tbl.ID AS VARCHAR(MAX)) + '</ID>' AS XML_Str
		FROM tbl
		
		UNION ALL
	    
		SELECT b.Marker + tbl.Marker
			, b.Level + 1
			, b.XML_Str + '<ID>' + tbl.ID + '</ID>' AS XML_Str
		FROM tbl
			INNER JOIN b ON tbl.marker & b.marker = 0
		WHERE b.Level < (SELECT TotalRows FROM a)
	)
	, c AS (
		SELECT Marker, Level, XML_Str
			, DENSE_RANK() OVER (PARTITION BY Marker ORDER BY XML_Str ASC) AS w
		FROM b
		WHERE Level > 1 OR @ReturnSingles = 1
	)
	, d AS (
		SELECT RANK() OVER(ORDER BY c.Level, c.Marker) AS Combination
			, CAST(c.XML_Str AS XML) AS [XML]
		FROM c
		WHERE w = 1
	)
	INSERT INTO @ret
	SELECT d.Combination
		, d.[XML] AS Combination_Items
		, c.value('.', 'INT') AS ID
	FROM d
		CROSS APPLY d.[XML].nodes('//ID') T(c)
	
	RETURN 
END

If you execute this (for example):

DECLARE @XML XML

SET @XML = '
<Data ID="123" />
<Data ID="456" />
<Data ID="789" />
'

SELECT * FROM dbo.fn_Combinations(@XML, default)

You will get this result:

Combination Combination_Items                       ID
----------- --------------------------------------- -----------
1           <ID>123</ID><ID>456</ID>                123
1           <ID>123</ID><ID>456</ID>                456
2           <ID>123</ID><ID>789</ID>                123
2           <ID>123</ID><ID>789</ID>                789
3           <ID>456</ID><ID>789</ID>                456
3           <ID>456</ID><ID>789</ID>                789
4           <ID>123</ID><ID>456</ID><ID>789</ID>    123
4           <ID>123</ID><ID>456</ID><ID>789</ID>    456
4           <ID>123</ID><ID>456</ID><ID>789</ID>    789