Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

generate combinations for a number Expand / Collapse
Author
Message
Posted Tuesday, October 13, 2009 6:07 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC 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
Posted Tuesday, October 13, 2009 6:19 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, April 8, 2014 6:13 AM
Points: 1,694, Visits: 19,550
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
Posted Tuesday, October 13, 2009 6:28 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC 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
Posted Wednesday, September 1, 2010 1:37 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, July 16, 2014 12:02 PM
Points: 46, Visits: 519



--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 »

Add to briefcase

Permissions Expand / Collapse