|
jepthy
Starting Member
1 Posts |
 Posted - 01/31/2013 : 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 |
|
Help with all possible unique set combinations
