Generate all possible number combinations for a provided list of numbers
-
Hi Experts,
I want to get "all possible number combinations" for provided list of numbers.
As an example, if I have 4 numbers: 1, 2, 3 and 4
For these numbers, I want to generate following numbers [which should be in order, so as to get unique list]:
1
1,2
1,3
1,4
1,2,3
1,2,4
1,3,4
1,2,3,4
2
1,2 (for 2,1) [which is duplicated]
2,3
2,4
1,2,3 (for 2,1,3) [which is duplicated]
1,2,4 (for 2,1,4) [which is duplicated]
1,2,3,4 (for 2,1,3,4 ) [which is duplicated]
3
1,3 (for 3,1) [which is duplicated]
2,3 (for 3,2) [which is duplicated]
3,4
1,2,3 (for 3,1,2) [which is duplicated]
1,3,4 (for 3,1,4) [which is duplicated]
1,2,3,4 (for 3,1,2,4) [which is duplicated]
4
1,4 (for 4,1) [which is duplicated]
2,4 (for 4,2) [which is duplicated]
3,4 (for 4,3) [which is duplicated]
1,2,4 (for 4,1,2) [which is duplicated]
1,3,4 (for 4,1,3) [which is duplicated]
2,3,4 (for 4,2,3)
1,2,3,4 (for 4,1,2,3) [which is duplicated]
Therefore unique list of possible number combinations (which are ordered) for 1,2,3 & 4 is:
1; 2; 3; 4;
(1,2); (1,3); (1,4); (2,3); (2,4); (3,4);
(1,2,3); (1,2,4); (1,3,4); (2,3,4)
(1,2,3,4)
Could this be achieved using sql? Thanks much for your help!
Thanks
-
I imagine if you take each number and use it as a basis for outer apply you'd get a cartesian product with every number comination but it's going to get very large very quickly.
-
This is a slightly long approach.....but this is one way you can do it :
;With CTE
As
(
Select 1 As Number Union Select 2Union Select 3 Union Select 4
),
CTE1
As
(
Select Cast(Number As Varchar) As N1, '' As N2, '' As N3, '' As N4 From CTE
Union ALL
Select DISTINCT Cast(a.Number As Varchar), Cast(b.Number As Varchar), '', '' From CTE As a CROSS JOIN CTE As b Where b.Number > a.Number
Union ALL
Select DISTINCT Cast(a.Number As Varchar), Cast(b.Number As Varchar), Cast(c.Number As Varchar), ''
From CTE As a CROSS JOIN CTE As b CROSS JOIN CTE As c
Where b.Number > a.Number AND c.Number > b.Number
Union ALL
Select DISTINCT Cast(a.Number As Varchar), Cast(b.Number As Varchar), Cast(c.Number As Varchar), Cast(d.Number As Varchar)
From CTE As a CROSS JOIN CTE As b CROSS JOIN CTE As c CROSS JOIN CTE As d
Where b.Number > a.Number AND c.Number > b.Number AND d.Number > c.Number
)
Select DISTINCT STUFF((Select ';' + N1 From CTE1 Where N2 = '' AND N3 = '' AND N4 = '' AND N1 <> '' FOR XML PATH('')),1,1,'') From CTE1
Where N2 = '' AND N3 = '' AND N4 = '' AND N1 <> ''
UNION ALL
Select DISTINCT STUFF((Select ';(' + N1 + ',' + N2 + ')' From CTE1 Where N3 = '' AND N4 = ''AND N1 <> '' AND N2 <>'' FOR XML PATH('')),1,1,'')
From CTE1 Where N3 = '' AND N4 = ''AND N1 <> '' AND N2 <>''
UNION ALL
Select DISTINCT STUFF((Select ';(' + N1 + ',' + N2 + ',' + N3 + ')' From CTE1 Where N4 = ''AND N1 <> '' AND N2 <>'' AND N3 <> '' FOR XML PATH('')),1,1,'')
From CTE1 Where N4 = ''AND N1 <> '' AND N2 <>'' AND N3 <> ''
UNION ALL
Select DISTINCT STUFF((Select ';(' + N1 + ',' + N2 + ',' + N3 + ',' + N4 + ')' From CTE1 Where N4 <> ''AND N1 <> '' AND N2 <>'' AND N3 <> '' FOR XML PATH('')),1,1,'')
From CTE1 Where N4 <> ''AND N1 <> '' AND N2 <>'' AND N3 <> ''
Let me see if I can better this solution with a better approach.
Hope this helps. 🙂
-
Another one to try
WITH Numbers(N) AS (SELECT N
FROM ( VALUES(1),(2),(3),(4) ) Numbers(N)),
Recur(N,Combination) AS (
SELECT N, CAST(N AS VARCHAR(1000))
FROM Numbers
UNION ALL
SELECT n.N,CAST(r.Combination + ',' + CAST(n.N AS VARCHAR(10)) AS VARCHAR(1000))
FROM Recur r
INNER JOIN Numbers n ON n.N > r.N)
SELECT Combination
FROM Recur
ORDER BY LEN(Combination),Combination;
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
-
Mark-101232 (4/2/2013)
Another one to tryWITH Numbers(N) AS (SELECT N
FROM ( VALUES(1),(2),(3),(4) ) Numbers(N)),
Recur(N,Combination) AS (
SELECT N, CAST(N AS VARCHAR(1000))
FROM Numbers
UNION ALL
SELECT n.N,CAST(r.Combination + ',' + CAST(n.N AS VARCHAR(10)) AS VARCHAR(1000))
FROM Recur r
INNER JOIN Numbers n ON n.N > r.N)
SELECT Combination
FROM Recur
ORDER BY LEN(Combination),Combination;
Very good one Mark......I was just about to post this....
After I posted my solution...I started thinking that I could do it recursively.....Its a lot easier and would be a lot better performance wise.
-
Thanks for the help, Mark & Vinu!
This solved my problem. Thanks for your responses.
-
Prashh (4/2/2013)
Thanks for the help, Mark & Vinu!This solved my problem. Thanks for your responses.
You'r welcome Prassh......You are always welcome to post on SSC and learn from here...like we all do....its always fun to learn like this. 🙂
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply