Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 Generate all possible number combinations for a provided list of numbers Rate Topic Display Mode Topic Options
Author
 Message
 Posted Friday, March 29, 2013 8:17 AM
 Forum Newbie Group: General Forum Members Last Login: Friday, April 12, 2013 11:57 AM Points: 2, Visits: 54
 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]:11,21,31,41,2,31,2,41,3,41,2,3,421,2 (for 2,1) [which is duplicated]2,32,41,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]31,3 (for 3,1) [which is duplicated]2,3 (for 3,2) [which is duplicated]3,41,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]41,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
Post #1436954
 Posted Friday, March 29, 2013 8:46 AM
 Mr or Mrs. 500 Group: General Forum Members Last Login: Friday, November 22, 2013 5:00 PM Points: 528, Visits: 966
 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.
Post #1436965
 Posted Tuesday, April 02, 2013 3:30 AM
 Ten Centuries Group: General Forum Members Last Login: Thursday, December 05, 2013 8:36 PM Points: 1,117, Visits: 1,560
 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 VijayanFor better and faster solutions please check..."How to post data/code on a forum to get the best help" - Jeff Moden
Post #1437768
 Posted Tuesday, April 02, 2013 5:10 AM
 SSCommitted Group: General Forum Members Last Login: Monday, December 16, 2013 1:28 AM Points: 1,694, Visits: 19,544
 Another one to try`WITH Numbers(N) AS (SELECT NFROM ( VALUES(1),(2),(3),(4) ) Numbers(N)),Recur(N,Combination) AS (SELECT N, CAST(N AS VARCHAR(1000)) FROM NumbersUNION ALLSELECT n.N,CAST(r.Combination + ',' + CAST(n.N AS VARCHAR(10)) AS VARCHAR(1000)) FROM Recur rINNER JOIN Numbers n ON n.N > r.N)SELECT CombinationFROM RecurORDER BY LEN(Combination),Combination;` ____________________________________________________How to get the best help on a forumhttp://www.sqlservercentral.com/articles/Best+Practices/61537Never approach a goat from the front, a horse from the rear, or a fool from any direction.
Post #1437822
 Posted Tuesday, April 02, 2013 5:48 AM
 Ten Centuries Group: General Forum Members Last Login: Thursday, December 05, 2013 8:36 PM Points: 1,117, Visits: 1,560
 Mark-101232 (4/2/2013)Another one to try`WITH Numbers(N) AS (SELECT NFROM ( VALUES(1),(2),(3),(4) ) Numbers(N)),Recur(N,Combination) AS (SELECT N, CAST(N AS VARCHAR(1000)) FROM NumbersUNION ALLSELECT n.N,CAST(r.Combination + ',' + CAST(n.N AS VARCHAR(10)) AS VARCHAR(1000)) FROM Recur rINNER JOIN Numbers n ON n.N > r.N)SELECT CombinationFROM RecurORDER 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 VijayanFor better and faster solutions please check..."How to post data/code on a forum to get the best help" - Jeff Moden
Post #1437842
 Posted Tuesday, April 02, 2013 9:29 AM
 Forum Newbie Group: General Forum Members Last Login: Friday, April 12, 2013 11:57 AM Points: 2, Visits: 54
 Thanks for the help, Mark & Vinu!This solved my problem. Thanks for your responses.
Post #1437967
 Posted Tuesday, April 02, 2013 10:04 PM
 Ten Centuries Group: General Forum Members Last Login: Thursday, December 05, 2013 8:36 PM Points: 1,117, Visits: 1,560
 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 VijayanFor better and faster solutions please check..."How to post data/code on a forum to get the best help" - Jeff Moden
Post #1438158

 Permissions