The Oracle (tm) Users' Co-Operative FAQ

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


Author's name: Frank Zhou

Author's Email:zhou328@comcast.net

Date written: 15th November 2006

Oracle version(s): 10.2

How do I display all the permutation 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