Permutation of Unque Record Set Combinations

  • I am trying to create a scalable procedure to generate all possible item combinations for set of items for a number of combinations. I know rows can be pivoted to columns in order to select distinct rows, but I do not want a combination of items used more than once and do not care about the position of the item in the unique set. I am trying to create this to be flexible, so permutations can be increased by adding a new row to the permutations table. Here is an example of the output, I am trying to achieve.

    Item | RowId

    -------- -------------------------------------------------

    Item A | 11B19E64-30E2-4D65-8B88-1E0A13C072F6

    Item B | 11B19E64-30E2-4D65-8B88-1E0A13C072F6

    Item A | 392FF181-65AD-47D6-9836-4AA1EB8A26D5

    Item C | 392FF181-65AD-47D6-9836-4AA1EB8A26D5

    Item A | CC235E34-1182-45A4-A1E1-57BCD64C2345

    Item D | CC235E34-1182-45A4-A1E1-57BCD64C2345

    Item B | 1BFFC9A8-299F-45B3-9B57-592B1B566901

    Item C | 1BFFC9A8-299F-45B3-9B57-592B1B566901

    Item B | FA410405-321C-488E-A3F8-92637C7C9F87

    Item D | FA410405-321C-488E-A3F8-92637C7C9F87

    Item C | 09B25D70-E446-4AA5-97C8-D03BD9EC69E5

    Item D | 09B25D70-E446-4AA5-97C8-D03BD9EC69E5

    Basically, for every combination of two items: A, B, C, D, I would like the outputs of AB, AC, AD, BC, BD and CD with a uniqueidentifier to mark the records as a combination. Any help would be greatly appreciated.

    Here is the code with which I have been working.

    -- Create temporary tables.

    CREATE TABLE #permutations(id TINYINT)

    CREATE TABLE #items (item varchar(20))

    -- Insert two rows for permutations.

    INSERT INTO #permutations

    VALUES (1)

    INSERT INTO #permutations

    VALUES (2)

    -- Insert Items to permutate

    INSERT INTO #items

    VALUES('Item A')

    INSERT INTO #items

    VALUES('Item B')

    INSERT INTO #items

    VALUES('Item C')

    INSERT INTO #items

    VALUES('Item D')

    -- Display the permutations.

    SELECT NEWID() AS rowId, itemPerm.item

    FROM (

    SELECT id, item

    FROM #items

    CROSS JOIN #permutations)

    AS itemPerm

    ORDER BY rowId

    -- Drop the temporary tables.

    DROP TABLE #permutations

    DROP TABLE #items

  • You can try something like this:

    -- Create temporary tables.

    CREATE TABLE #permutations(id TINYINT)

    CREATE TABLE #items (item varchar(20))

    -- Insert two rows for permutations.

    INSERT INTO #permutations

    VALUES (1)

    INSERT INTO #permutations

    VALUES (2)

    -- Insert Items to permutate

    INSERT INTO #items

    VALUES('Item A')

    INSERT INTO #items

    VALUES('Item B')

    INSERT INTO #items

    VALUES('Item C')

    INSERT INTO #items

    VALUES('Item D')

    DECLARE @Permutations INT = (SELECT MAX(id) FROM #permutations)

    DECLARE @T TABLE (Items VARCHAR(8000), rowid VARCHAR(200))

    ;WITH nTuples (n, Tuples) AS (

    SELECT 1, CAST(RIGHT(item, 1) AS VARCHAR(8000))

    FROM #items

    UNION ALL

    SELECT 1 + n.n, RIGHT(t.item, 1) + ',' + n.Tuples

    FROM #items t JOIN nTuples n ON t.item <> n.Tuples

    WHERE n < @Permutations AND CHARINDEX(RIGHT(t.item, 1), n.Tuples) = 0

    )

    INSERT INTO @T

    SELECT Tuples, NEWID()

    FROM nTuples

    WHERE n = 2

    SELECT rowid, Item

    FROM @T

    CROSS APPLY DelimitedSplit8K(Items, ',')

    -- Drop the temporary tables.

    DROP TABLE #permutations

    DROP TABLE #items

    The nTuples rCTE is explained here (Generating n-Tuples with SQL[/url]) and should be OK until you start going to high numbers of permutations, at which time it will begin producing huge row sets.

    The interesting thing about this is that it only works with the temp table (in my case table variable) because the minute CROSS APPLY is used, it generates new GUIDs for the new rows, regardless of how you try to avoid it.

    DelimitedSplit8K is explained here: http://www.sqlservercentral.com/articles/Tally+Table/72993/ and if you haven't seen it before it is an indispensible tool in anyone's tool set.


    My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

    My thought question: Have you ever been told that your query runs too fast?

    My advice:
    INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
    The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

    Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
    Since random numbers are too important to be left to chance, let's generate some![/url]
    Learn to understand recursive CTEs by example.[/url]
    [url url=http://www.sqlservercentral.com/articles/St

Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply