SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Help with all possible unique set combinations
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

jepthy
Starting Member

1 Posts

Posted - 01/31/2013 :  18:25:20  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.28 seconds. Powered By: Snitz Forums 2000