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.
Code:
-- =============================================
-- 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):
Code:
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:
Code:
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