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