SQL Server administration and T-SQL development, Web Programming with ASP.NET, HTML5 and Javascript, Windows Phone 8 app development, SAP Smartforms and ABAP Programming, Windows 7, Visual Studio and MS Office software SQL Server and T-SQL Development Tutorials
Development resources, articles, tutorials, samples, codes and tools for .Net, SQL Server, Windows, Windows Phone, SAP and ABAP, like SAP UI5, Screen Personas, etc.




download SQL Server 2016
download SQL Server 2014
download SQL Server 2012



Select Combinations of Data using SQL Query

Database developers can build SQL Select query to generate and return all possible combinations of data in SQL Server by using method shown in this SQL tutorial with cross joins and applying a simple logic.
In your application, you might have certain numbers, users, colors that you want to select a number of given items.
For example, from existing 10 items you want to group any 3 of them. So you have exactly (10*9*8)/(3*2) = 120 possible combinations.
Let's create a Transact-SQL query which will return all possible combinations without repetition calculated by the above simple mathematical calculation or formula.

Let's assume in our SQL Server database we have a colors table where a number of color names are stored. Here I share the Create Table DDL statement and the Insert DML commands which will populate the SQL database table with sample data.

create table Colors (color varchar(25))

insert into Colors values ('White'),('Blue'),('Orange')
insert into Colors values ('Red'),('Black'),('Brown'),('Purple')

Even to make the tutorial sample for combination query, let's insert same colors again in the Colors table.
Our SQL query should take these repeating values as a single data row. Because we will generate combination of these items without repetition.

insert into Colors values ('White'),('Blue'),('Orange')

By using SQL Server DENSE_RANK() window function with Distinct clause, I can easily get a unique list of colors.

select distinct rn = DENSE_RANK() over (order by color), color from Colors

Select SQL query for combination calculation in SQL Server

I will use this colors list from sample database table using SQL CTE expressions and cross join the CTE set 3 times to get a result set for permutation of all available colors in a 3 color slots.

;with combinationTable as (
 select distinct rn = DENSE_RANK() over (Order By color), color from Colors
)
select c1.color, c2.color, c3.color
from combinationTable c1, combinationTable c2, combinationTable c3

As the count of rows in the returned result set shows (343 rows), SQL programmers will have the permutation of 7 for 3
Or explicitely, by cross joining the table to itself the result set will return all possible permutations of 3 colors (items) from a set of 7
343 rows = 7 * 7 * 7

But our initial requirement for this SQL tutorial is for obtaining the combination of these items not for the permutation.

By applying WHERE clause with suitable criteria, SQL programmers can have the combination set easily in this SQL query.

;with combinationTable as (
 select distinct rn = DENSE_RANK() over (Order By color), color from Colors
)
select c1.color, c2.color, c3.color
from combinationTable c1, combinationTable c2, combinationTable c3
where
 c1.rn < c2.rn and
 c2.rn < c3.rn
order by c1.rn, c2.rn, c3.rn

Now the result set returns "7 choose 3" for combination of 3 colors out of 7 possible without repetition.
Number of rows : (7*6*5)/(3*2*1) = 35

code for combination of colors using SQL query

Of course the "From clause" and "Where clause" will be modified if you want to choose 4 colors instead of 3.
SQL Server developers will add additional CTE table to the FROM clause using new CROSS JOIN.
And WHERE clause will be modified for the new item.

Here is the SQL combination query for 4 items which has an additional "combination number" field and sorted randomly by the use of SQL NewID() function

;with combination as (
 select distinct rn = DENSE_RANK() over (Order By color), color from Colors
)
select
 combination_no = ROW_NUMBER() Over (Order By NEWID()),
 c1.color, c2.color, c3.color, c4.color
from combination c1, combination c2, combination c3, combination c4
where
 c1.rn < c2.rn and
 c2.rn < c3.rn and
 c3.rn < c4.rn
order by combination_no --c1.rn, c2.rn, c3.rn, c4.rn

For SQL Server developers, it might be also interesting how to calculate factorial of an integer using SQL user-defined function







Related SQL Resources

SQL Server Articles

SQL Server 2012

SQL Server Tools

SQL Blog

SQL Server 2008 Blog

Certification Exams Blog

Reporting Services Blog

Analysis Services Blog

MS SQL Server Forums







Copyright © 2004 - 2017 Eralper YILMAZ. All rights reserved.
Community Server by Telligent Systems