All Combinations of Numbers– T-SQL


This sample code is to show a way for how to get all combination of numbers with T-SQL. Joe Celko has many great solutions with standard compliant SQL query. His SQL books is on my never finishing reading list.
Here is the one I have learned:


;WITH mycte(i)
AS (
SELECT 1 as i
Union ALL
SELECT i +1 as seq FROM mycte WHERE i < 4)

SELECT m1.i AS n1, m2.i AS n2, m3.i AS n3, m4.i AS n4
FROM mycte AS m1
      CROSS JOIN mycte AS m2
      CROSS JOIN mycte AS m3
      CROSS JOIN mycte AS m4   
 WHERE m1.i NOT IN (m2.i, m3.i, m4.i)
 AND m2.i NOT IN (m3.i, m4.i)
 AND m3.i NOT IN (m4.i)
 

 --http://www.sqlservercentral.com/blogs/steve_jones/2011/06/07/combinations-and-permutations/
 /*
 n1	n2	n3	n4
4	1	2	3
3	1	2	4
4	1	3	2
2	1	3	4
3	1	4	2
2	1	4	3
4	2	1	3
3	2	1	4
4	2	3	1
1	2	3	4
3	2	4	1
1	2	4	3
4	3	1	2
2	3	1	4
4	3	2	1
1	3	2	4
2	3	4	1
1	3	4	2
3	4	1	2
2	4	1	3
3	4	2	1
1	4	2	3
2	4	3	1
1	4	3	2

 */

 
Advertisements


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s