Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums  SQL Server 2008 Forums  Transact-SQL (2008)  Help with all possible unique set combinations

Author  Topic  jepthy Starting Member 1 Post Posted - 2013-01-31 : 18:25:20 I need help writing a query to get all possible combinations for a given set of data. For example, if I were given the data set of 1,2,3 (as unique rows), I would need the following output 1 2 3 1, 2 1, 3 2, 3 1, 2, 3 I don't need 1,2 and 2,1. I just need unique sets. I have been working on a stored procedure where you can pass in the number 3, and it will attempt to loop through for 1 through 3, and come up with all of these possible values. However, it bogs down if I try to go from 1-20 because of all of the different possibilities. Idealy, the output would be structured something like this: ID, Value 1, 1 2, 2 3, 3 4, 1 4, 2 5, 1 5, 3 6, 2 6, 3 7, 1 7, 2 7, 3 Where the ID is a unique ID for the set, and the value is a single row. Any suggestions? This is what I have so far, but I think there might be a way to do it with CTE that I am just missing somewhere which would be more effective. CREATE PROCEDURE P_Get_Unique_Combinations @N TinyInt AS BEGIN DECLARE @X TinyInt; DECLARE @Groups TABLE ( GroupID Int Identity(1,1), LinkedGroupID Int, Level Int ); DECLARE @Values Table ( GroupID Int, Value Int ); DECLARE @Inserted TABLE ( GroupID Int, LinkedGroupID Int ); --Insert our initial Values SET @X = 1; WHILE @X<=@N BEGIN DECLARE @GroupID Int; INSERT INTO @Groups (Level) VALUES (1); SET @GroupID = SCOPE_IDENTITY(); INSERT INTO @Values ( GroupID, Value ) VALUES ( @GroupID, @X ); SET @X = @X+1; END SET @X = 2; WHILE @X<=@N BEGIN --Get rid of our Output table DELETE FROM @Inserted; --Insert our levels INSERT INTO @Groups ( Level, LinkedGroupID ) OUTPUT inserted.GroupID, inserted.LinkedGroupID INTO @Inserted SELECT @X AS Level, G.GroupID FROM @Groups G LEFT OUTER JOIN ( SELECT GroupID FROM @Values WHERE Value = @X ) B ON B.GroupID = G.GroupID WHERE G.Level < @X AND B.GroupID IS NULL; --Insert our Values INSERT INTO @Values ( GroupID, Value ) SELECT I.GroupID, V.Value FROM @Inserted I JOIN @Values V ON V.GroupID = I.LinkedGroupID LEFT OUTER JOIN ( SELECT GroupID FROM @Values WHERE Value = @X ) B ON B.GroupID = I.LinkedGroupID WHERE B.GroupID IS NULL; --Add our value record INSERT INTO @Values ( GroupID, Value ) SELECT GroupID, @X FROM @Inserted; SET @X = @X+1; END SELECT G.GroupID, G.Level, V.Value FROM @Values V JOIN @Groups G ON G.GroupID = V.GroupID ORDER BY G.Level, G.GroupID, V.Value; END