generate combinations for a number

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » T-SQL (SS2K5) » generate combinations for a number

generate combinations for a number

Rate Topic

Display Mode

Topic Options

Author

Message

RPP

Posted Tuesday, October 13, 2009 6:07 AM

SSC Journeyman

Group: General Forum Members
Last Login: Tuesday, March 12, 2013 9:02 AM
Points: 84, Visits: 42

if 1,2,3,4 i provide as input, how to generate all the combinations of this number like 1,2,3,4 ; 1,2,4,3 ; 1,3,2,4 ; 1,3,4,2 ; 1,4,2,3 ; 1,4,3,2 ; 2,1,3,4; 2,1,4,3 ; 2,3,1,4... i interchanged last two digits and N-3 increased to 1 for next sequence. this is example of 4 digits, if i enter 10 digits it should provide all the combinations. Could some one help me in providing solution for this. while i am using sql server 2005.

All the numbers will be in series like for 10 digits.. 1,2,3,4,5,6,7,8,9,10. so need to find combinations of these.

thanks in advance,
rpp

Post #802089

Mark-101232

Posted Tuesday, October 13, 2009 6:19 AM

SSCommitted

Group: General Forum Members
Last Login: Today @ 2:02 PM
Points: 1,527, Visits: 18,448

Can you explain why you want to do this. The number of combinations for 10 digits is huge.

____________________________________________________

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 #802092

RPP

Posted Tuesday, October 13, 2009 6:28 AM

SSC Journeyman

Group: General Forum Members
Last Login: Tuesday, March 12, 2013 9:02 AM
Points: 84, Visits: 42

there could be more than 10 digits also. My main requirement is i need all the combinations to be generated. the series i found like if last (N) and last-1 (N-1) digits are interchanged, in next sequence N-2 can be incremented and generate sequence with missing numbers like in series 1,2,3,4, second sequence wil be 1,2,4,3 ( interchange last two digits). next 1,3,2,4(N-3 digit is incremented and missing numbers 2,4 are placed). it could give all the combinations. but in coding wise how to arrive i couldnt get.

pls provide procedure to generate all the combinations if input is given for @a='1,2,3,4,5,6,7'.. likewise

Post #802099

TJ-356724

Posted Wednesday, September 01, 2010 1:37 PM

SSC Rookie

Group: General Forum Members
Last Login: Friday, February 15, 2013 8:45 AM
Points: 40, Visits: 494

--drop table @temp
declare @temp table(tag smallint primary key)

insert into @temp(tag)
values(1)

insert into @temp(tag)
values(2)

insert into @temp(tag)
values(3)

insert into @temp(tag)
values(4)

insert into @temp(tag)
values(5)

insert into @temp(tag)
values(6)

insert into @temp(tag)
values(7)

insert into @temp(tag)
values(8)

insert into @temp(tag)
values(9)

insert into @temp(tag)
values(10)

--2 records
--Time 0 sec
If (select count(*) from @temp)=2
begin
select t1.tag as t1 ,t2.tag as t2 from @temp t1
cross join @temp t2
where t1.tag <> t2.tag
--order by t1.tag,t2.tag
end

--3 records
--Time 0 sec
If (select count(*) from @temp)=3
begin
select t1.tag as t1 ,t2.tag as t2, t3.tag as t3 from @temp t1
cross join @temp t2
cross join @temp t3
where t1.tag <> t2.tag
and t1.tag <> t3.tag
and t2.tag <> t3.tag
--order by t1.tag,t2.tag,t3.tag
end

--4 records
--Time 0 sec
If (select count(*) from @temp)=4
begin
select t1.tag as t1 ,t2.tag as t2, t3.tag as t3 ,t4.tag as t4 from @temp t1
cross join @temp t2
cross join @temp t3
cross join @temp t4

where t1.tag <> t2.tag
and t1.tag <> t3.tag
and t1.tag <> t4.tag

and t2.tag <> t3.tag
and t2.tag <> t4.tag

and t3.tag <> t4.tag

--order by t1.tag,t2.tag,t3.tag,t4.tag
end

--5 records
--Time 1 sec
If (select count(*) from @temp)=5
begin
select t1.tag as t1 ,t2.tag as t2, t3.tag as t3 ,t4.tag as t4 ,t5.tag as t5 from @temp t1
cross join @temp t2
cross join @temp t3
cross join @temp t4
cross join @temp t5

where t1.tag <> t2.tag
and t1.tag <> t3.tag
and t1.tag <> t4.tag
and t1.tag <> t5.tag

and t2.tag <> t3.tag
and t2.tag <> t4.tag
and t2.tag <> t5.tag

and t3.tag <> t4.tag
and t3.tag <> t5.tag

and t4.tag <> t5.tag

--order by t1.tag,t2.tag,t3.tag,t4.tag,t5.tag
end

--6 records
--Time 2 sec
If (select count(*) from @temp)=6
begin
select t1.tag as t1 ,t2.tag as t2, t3.tag as t3 ,t4.tag as t4 ,t5.tag as t5,t6.tag as t6 from @temp t1
cross join @temp t2
cross join @temp t3
cross join @temp t4
cross join @temp t5
cross join @temp t6
where t1.tag <> t2.tag
and t1.tag <> t3.tag
and t1.tag <> t4.tag
and t1.tag <> t5.tag
and t1.tag <> t6.tag

and t2.tag <> t3.tag
and t2.tag <> t4.tag
and t2.tag <> t5.tag
and t2.tag <> t6.tag

and t3.tag <> t4.tag
and t3.tag <> t5.tag
and t3.tag <> t6.tag

and t4.tag <> t5.tag
and t4.tag <> t6.tag

and t5.tag <> t6.tag

--order by t1.tag,t2.tag,t3.tag,t4.tag,t5.tag,t6.tag
end

--7 records
--Time 6 sec
If (select count(*) from @temp)=7
begin
select t1.tag as t1 ,t2.tag as t2, t3.tag as t3 ,t4.tag as t4 ,t5.tag as t5,t6.tag as t6 ,t7.tag as t7 from @temp t1
cross join @temp t2
cross join @temp t3
cross join @temp t4
cross join @temp t5
cross join @temp t6
cross join @temp t7
where t1.tag <> t2.tag
and t1.tag <> t3.tag
and t1.tag <> t4.tag
and t1.tag <> t5.tag
and t1.tag <> t6.tag
and t1.tag <> t7.tag

and t2.tag <> t3.tag
and t2.tag <> t4.tag
and t2.tag <> t5.tag
and t2.tag <> t6.tag
and t2.tag <> t7.tag

and t3.tag <> t4.tag
and t3.tag <> t5.tag
and t3.tag <> t6.tag
and t3.tag <> t7.tag

and t4.tag <> t5.tag
and t4.tag <> t6.tag
and t4.tag <> t7.tag

and t5.tag <> t6.tag
and t5.tag <> t7.tag

and t6.tag <> t7.tag

--order by t1.tag,t2.tag,t3.tag,t4.tag,t5.tag,t6.tag,t7.tag
end

--8 records
--Time 17 sec
If (select count(*) from @temp)=8
begin
select t1.tag as t1 ,t2.tag as t2, t3.tag as t3,
t4.tag as t4 ,t5.tag as t5,
t6.tag as t6 ,t7.tag as t7,
t8.tag as t8
from @temp t1
cross join @temp t2
cross join @temp t3
cross join @temp t4
cross join @temp t5
cross join @temp t6
cross join @temp t7
cross join @temp t8
where t1.tag <> t2.tag
and t1.tag <> t3.tag
and t1.tag <> t4.tag
and t1.tag <> t5.tag
and t1.tag <> t6.tag
and t1.tag <> t7.tag
and t1.tag <> t8.tag

and t2.tag <> t3.tag
and t2.tag <> t4.tag
and t2.tag <> t5.tag
and t2.tag <> t6.tag
and t2.tag <> t7.tag
and t2.tag <> t8.tag

and t3.tag <> t4.tag
and t3.tag <> t5.tag
and t3.tag <> t6.tag
and t3.tag <> t7.tag
and t3.tag <> t8.tag

and t4.tag <> t5.tag
and t4.tag <> t6.tag
and t4.tag <> t7.tag
and t4.tag <> t8.tag

and t5.tag <> t6.tag
and t5.tag <> t7.tag
and t5.tag <> t8.tag

and t6.tag <> t7.tag
and t6.tag <> t8.tag

and t7.tag <> t8.tag

--order by t1.tag,t2.tag,t3.tag,t4.tag,t5.tag,t6.tag,t7.tag,t8.tag
end

--9 records
--Time 22 sec
If (select count(*) from @temp)=9
begin
select t1.tag as t1 ,t2.tag as t2,t3.tag as t3,
t4.tag as t4,t5.tag as t5,
t6.tag as t6,t7.tag as t7,
t8.tag as t8,t9.tag as t9
from @temp t1
cross join @temp t2
cross join @temp t3
cross join @temp t4
cross join @temp t5
cross join @temp t6
cross join @temp t7
cross join @temp t8
cross join @temp t9
where t1.tag <> t2.tag
and t1.tag <> t3.tag
and t1.tag <> t4.tag
and t1.tag <> t5.tag
and t1.tag <> t6.tag
and t1.tag <> t7.tag
and t1.tag <> t8.tag
and t1.tag <> t9.tag

and t2.tag <> t3.tag
and t2.tag <> t4.tag
and t2.tag <> t5.tag
and t2.tag <> t6.tag
and t2.tag <> t7.tag
and t2.tag <> t8.tag
and t2.tag <> t9.tag

and t3.tag <> t4.tag
and t3.tag <> t5.tag
and t3.tag <> t6.tag
and t3.tag <> t7.tag
and t3.tag <> t8.tag
and t3.tag <> t9.tag

and t4.tag <> t5.tag
and t4.tag <> t6.tag
and t4.tag <> t7.tag
and t4.tag <> t8.tag
and t4.tag <> t9.tag

and t5.tag <> t6.tag
and t5.tag <> t7.tag
and t5.tag <> t8.tag
and t5.tag <> t9.tag

and t6.tag <> t7.tag
and t6.tag <> t8.tag
and t6.tag <> t9.tag

and t7.tag <> t8.tag
and t7.tag <> t9.tag

and t8.tag <> t9.tag

--order by t1.tag,t2.tag,t3.tag,t4.tag,t5.tag,t6.tag,t7.tag,t8.tag,t9.tag
end

--10 records
--Time 1 min 45 sec
If (select count(*) from @temp)=10
begin
select t1.tag as t1 ,t2.tag as t2,t3.tag as t3,
t4.tag as t4,t5.tag as t5,
t6.tag as t6,t7.tag as t7,
t8.tag as t8,t9.tag as t9,
t10.tag as t10
from @temp t1
cross join @temp t2
cross join @temp t3
cross join @temp t4
cross join @temp t5
cross join @temp t6
cross join @temp t7
cross join @temp t8
cross join @temp t9
cross join @temp t10

where t1.tag <> t2.tag
and t1.tag <> t3.tag
and t1.tag <> t4.tag
and t1.tag <> t5.tag
and t1.tag <> t6.tag
and t1.tag <> t7.tag
and t1.tag <> t8.tag
and t1.tag <> t9.tag
and t1.tag <> t10.tag

and t2.tag <> t3.tag
and t2.tag <> t4.tag
and t2.tag <> t5.tag
and t2.tag <> t6.tag
and t2.tag <> t7.tag
and t2.tag <> t8.tag
and t2.tag <> t9.tag
and t2.tag <> t10.tag

and t3.tag <> t4.tag
and t3.tag <> t5.tag
and t3.tag <> t6.tag
and t3.tag <> t7.tag
and t3.tag <> t8.tag
and t3.tag <> t9.tag
and t3.tag <> t10.tag

and t4.tag <> t5.tag
and t4.tag <> t6.tag
and t4.tag <> t7.tag
and t4.tag <> t8.tag
and t4.tag <> t9.tag
and t4.tag <> t10.tag

and t5.tag <> t6.tag
and t5.tag <> t7.tag
and t5.tag <> t8.tag
and t5.tag <> t9.tag
and t5.tag <> t10.tag

and t6.tag <> t7.tag
and t6.tag <> t8.tag
and t6.tag <> t9.tag
and t6.tag <> t10.tag

and t7.tag <> t8.tag
and t7.tag <> t9.tag
and t7.tag <> t10.tag

and t8.tag <> t9.tag
and t8.tag <> t10.tag

and t9.tag <> t10.tag

--order by t1.tag,t2.tag,t3.tag,t4.tag,t5.tag,t6.tag,t7.tag,t8.tag,t9.tag,t10.tag
end

--select tag as t1,t2,t3,t4,t5,t6,t7 from @temp1
--group by tag,t2,t3,t4,t5,t6,t7

Post #979107

« Prev Topic | Next Topic »

Permissions