generate combinations for a number
-
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
-
Can you explain why you want to do this. The number of combinations for 10 digits is huge.
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
-
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
-
--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
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply