Generate All Combinations with in a set of 3

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Generate All Combinations with in a set of 3

Author

Topic

hksharmaa
Starting Member

16 Posts

Posted - 2011-03-07 : 02:59:29

Hi All...

Is there any way to generate all combinations in a set of 3 for numbers between 1 to 5...I have Table 1 which consist of only 1 column and values from 1 to 5. I want the result in Table 2 which has 2 column
For Eg..

My Table A=
1
2
3
4
5

Result TABLE B
Col.1 COL. 2

1 COMB1
2 COMB1
3 COMB1

1 COMB2
2 COMB2
4 COMB2

1 COMB3
2 COMB3
5 COMB3
. .
. .
And so on..

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-03-07 : 03:41:31

n C r ?


with TableA as
(
	select	col = 1	union all
	select	col = 2	union all
	select	col = 3	union all
	select	col = 4	union all
	select	col = 5
)
select	col, row_no as combi
from	(
		select	row_no = row_number() over (order by c1.col, c2.col, c3.col),
			col1 = c1.col, col2 = c2.col, col3 = c3.col
		from	TableA c1
			inner join TableA c2	on	c1.col	<> c2.col
			inner join TableA c3	on	c1.col	<> c3.col	and	c2.col	<> c3.col
	) d
	unpivot
	(	col for
		cols in (col1, col2, col3)
	) p

KH
[spoiler]Time is always against us[/spoiler]

hksharmaa
Starting Member

16 Posts

Posted - 2011-03-07 : 05:31:57

Thanks for the effort..It worked...Is theera any way to show only unique set only...and also if possible is theer any way to make a general quary fot this...I want make it hardcore for 3 set only..It can be 4 or 2...Is there any general way of writing it...
Suppose i want to get a set 0f 5 from 1 to 20 numbers..ao set of 4...??n C r at run time???

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2011-03-07 : 19:22:07

Combinations have no ordering; permutations do.

CREATE TABLE Foobar (i INTEGER NOT NULL PRIMARY KEY);

–- combinations
SELECT F1.i AS c1, F2.i AS c2, F3.i AS c3
FROM Foobar AS F1, Foobar AS F2, Foobar AS F3
WHERE F1.i < F2.i
AND F2.i < F3.i;

–- permutations
SELECT F1.i AS c1, F2.i AS c2, F3.i AS c3
FROM Foobar AS F1, Foobar AS F2, Foobar AS F3
WHERE F1.i NOT IN (F2.i, F3.i)
AND F2.i <> F3.i;

--CELKO--
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources