Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Generate all possible number combinations for a provided list of numbers Expand / Collapse
Author
Message
Posted Friday, March 29, 2013 8:17 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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]:

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
Post #1436954
Posted Friday, March 29, 2013 8:46 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Friday, November 21, 2014 12:57 PM
Points: 546, Visits: 1,062
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 2, 2013 3:30 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, October 28, 2014 6:04 AM
Points: 1,127, Visits: 1,599
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
Post #1437768
Posted Tuesday, April 2, 2013 5:10 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, November 11, 2014 3:53 AM
Points: 1,678, Visits: 19,555
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;



____________________________________________________

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

Never approach a goat from the front, a horse from the rear, or a fool from any direction.
Post #1437822
Posted Tuesday, April 2, 2013 5:48 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, October 28, 2014 6:04 AM
Points: 1,127, Visits: 1,599
Mark-101232 (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
Post #1437842
Posted Tuesday, April 2, 2013 9:29 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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 2, 2013 10:04 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, October 28, 2014 6:04 AM
Points: 1,127, Visits: 1,599
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
Post #1438158
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse