SQL Server 2005 - Combinations

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

---