How do I display all the permutation Or Combinations of data in a SQL Statement

The Oracle (tm) Users' Co-Operative FAQ

How do I display all the permutations or combinations of data in a SQL Statement.

Back to index of questions

The following SQL queries can be used to display all the permutations or combinations of data in your table.

SQL> variable NumChars  number

SQL>  variable LengthOfchar number

SQL> exec :NumChars  := &numOfChars

Enter value for numofchars: 3

PL/SQL procedure successfully completed.

SQL> exec :LengthOfchar := &lengthofChars

Enter value for lengthofchars: 2

The following is the testing table :

select chr(ascii('A')+level-1)  n from dual connect by level <=:NumChars

select permutations  from

    (select replace (sys_connect_by_path( n, ',' ) , ',' ) permutations

         from

        (select chr(ascii('A')+level-1)  n from dual connect by level <=:NumChars ) YourTable

         connect by nocycle n != prior n

        )

        where length(permutations) = :LengthOfchar;

PERMUTATIONS                                                                    

--------------------------------------------------------------------------------

AB                                                                              

AC                                                                              

BA                                                                              

BC                                                                              

CA                                                                              

CB                                                                              

6 rows selected.

select combinations

from

    (select replace (sys_connect_by_path( n, ',' ) , ',' ) combinations

    from

    (select chr(ascii('A')+level-1)  n from dual connect by level <=:NumChars ) YourTable

     connect by n  > prior n

    )

    where length(combinations) = :LengthOfchar;

COMBINATIONS                                                                    

--------------------------------------------------------------------------------

AB                                                                              

AC                                                                              

BC                                                                              

SQL> spool off

Back to top

Back to index of questions