InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comTue, 21 May 2013 22:16:52 GMT20http://www.sqlservercentral.com/Forums/Topic802089-338-1.aspx--drop table @tempdeclare @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 secIf (select count(*) from @temp)=2beginselect t1.tag as t1 ,t2.tag as t2 from @temp t1cross join @temp t2where t1.tag <> t2.tag --order by t1.tag,t2.tag end--3 records--Time 0 secIf (select count(*) from @temp)=3beginselect t1.tag as t1 ,t2.tag as t2, t3.tag as t3 from @temp t1cross join @temp t2cross join @temp t3where 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 secIf (select count(*) from @temp)=4beginselect t1.tag as t1 ,t2.tag as t2, t3.tag as t3 ,t4.tag as t4 from @temp t1cross join @temp t2cross join @temp t3cross join @temp t4where 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 secIf (select count(*) from @temp)=5beginselect t1.tag as t1 ,t2.tag as t2, t3.tag as t3 ,t4.tag as t4 ,t5.tag as t5 from @temp t1cross join @temp t2cross join @temp t3cross join @temp t4cross join @temp t5where 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 secIf (select count(*) from @temp)=6beginselect 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 t1cross join @temp t2cross join @temp t3cross join @temp t4cross join @temp t5cross join @temp t6where 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 secIf (select count(*) from @temp)=7beginselect 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 t1cross join @temp t2cross join @temp t3cross join @temp t4cross join @temp t5cross join @temp t6cross join @temp t7where 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.tagend--8 records--Time 17 secIf (select count(*) from @temp)=8beginselect 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 t1cross join @temp t2cross join @temp t3cross join @temp t4cross join @temp t5cross join @temp t6cross join @temp t7cross join @temp t8where 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.tagend--9 records--Time 22 secIf (select count(*) from @temp)=9beginselect 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 t1cross join @temp t2cross join @temp t3cross join @temp t4cross join @temp t5cross join @temp t6cross join @temp t7cross join @temp t8cross join @temp t9where 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.tagend--10 records--Time 1 min 45 secIf (select count(*) from @temp)=10beginselect 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 t1cross join @temp t2cross join @temp t3cross join @temp t4cross join @temp t5cross join @temp t6cross join @temp t7cross join @temp t8cross join @temp t9cross join @temp t10where 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.tagend--select tag as t1,t2,t3,t4,t5,t6,t7 from @temp1--group by tag,t2,t3,t4,t5,t6,t7Wed, 01 Sep 2010 13:37:08 GMTTJ-356724http://www.sqlservercentral.com/Forums/Topic802089-338-1.aspxthere 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'.. likewiseTue, 13 Oct 2009 06:28:58 GMTRPPhttp://www.sqlservercentral.com/Forums/Topic802089-338-1.aspxCan you explain why you want to do this. The number of combinations for 10 digits is huge.Tue, 13 Oct 2009 06:19:40 GMTMark-101232http://www.sqlservercentral.com/Forums/Topic802089-338-1.aspxif 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,rppTue, 13 Oct 2009 06:07:41 GMTRPP