February 13, 2013 at 12:26 pm
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
February 13, 2013 at 5:51 pm
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 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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy