

Forum Newbie
Group: General Forum Members
Last Login: Thursday, January 14, 2016 1:59 AM
Points: 3,
Visits: 70


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




Mr or Mrs. 500
Group: General Forum Members
Last Login: Monday, October 24, 2016 9:51 AM
Points: 551,
Visits: 1,099


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.




Ten Centuries
Group: General Forum Members
Last Login: Thursday, February 18, 2016 6:33 AM
Points: 1,127,
Visits: 1,616


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.
Vinu Vijayan
For better and faster solutions please check..."How to post data/code on a forum to get the best help"  Jeff Moden




SSCrazy
Group: General Forum Members
Last Login: Yesterday @ 8:23 AM
Points: 2,014,
Visits: 22,604


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




Ten Centuries
Group: General Forum Members
Last Login: Thursday, February 18, 2016 6:33 AM
Points: 1,127,
Visits: 1,616


Mark101232 (4/2/2013)
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;
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.
Vinu Vijayan
For better and faster solutions please check..."How to post data/code on a forum to get the best help"  Jeff Moden




Forum Newbie
Group: General Forum Members
Last Login: Thursday, January 14, 2016 1:59 AM
Points: 3,
Visits: 70


Thanks for the help, Mark & Vinu! This solved my problem. Thanks for your responses.




Ten Centuries
Group: General Forum Members
Last Login: Thursday, February 18, 2016 6:33 AM
Points: 1,127,
Visits: 1,616


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.
Vinu Vijayan
For better and faster solutions please check..."How to post data/code on a forum to get the best help"  Jeff Moden



