Generate all possible number combinations for a provided list of numbers

 Author Message Prashh SSC Journeyman Group: General Forum Members Points: 89 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]: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 Erin Ramsay SSCertifiable Group: General Forum Members Points: 5588 Visits: 1126 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. vinu512 SSCrazy Eights Group: General Forum Members Points: 8575 Visits: 1626 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 ;-) Mark Cowne SSChampion Group: General Forum Members Points: 14782 Visits: 26575 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;` ____________________________________________________Deja View - The strange feeling that somewhere, sometime you've optimised this query beforeHow to get the best help on a forumhttp://www.sqlservercentral.com/articles/Best+Practices/61537 vinu512 SSCrazy Eights Group: General Forum Members Points: 8575 Visits: 1626 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 ;-) Prashh SSC Journeyman Group: General Forum Members Points: 89 Visits: 70 Thanks for the help, Mark & Vinu!This solved my problem. Thanks for your responses. vinu512 SSCrazy Eights Group: General Forum Members Points: 8575 Visits: 1626 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 ;-)