Results 1 to 1 of 1

Thread: SQL Server 2005 - Combinations

  1. #1

    Thread Starter
    PowerPoster
    Join Date
    Feb 2002
    Location
    Canada, Toronto
    Posts
    5,802

    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
    Last edited by CVMichael; Sep 12th, 2011 at 05:51 PM.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width