The Oracle (tm) Users' Co-Operative FAQ

How do I display combinations using the table format in a SQL Statement?


Author's name: Frank Zhou

Author's Email:zhou328@comcast.net

Date written: 12th January 2007

Oracle version(s): 10.2

How do I display combinations using the table format in a SQL Statement?

Back to index of questions


1)      The following SQL pattern can be used to display all the combinations for a predetermined number of columns using the table format. (Usage : This pattern can be used to display all the table column combinations that can be defined as not null or the combinations of index that can be created by using these columns)

SQL> COLUMN COL_1 FORMAT A8

SQL> COLUMN COL_2 FORMAT A8

SQL> COLUMN COL_3 FORMAT A8

SQL> variable input number

SQL> exec :input := 3

 

SELECT

MAX(CASE WHEN comb = 'A' THEN comb END) AS COL_1,

MAX(CASE WHEN comb = 'B' THEN comb END) AS COL_2,

MAX(CASE WHEN comb = 'C' THEN comb END) AS COL_3

FROM

(SELECT

SUBSTR(comb,

INSTR(comb, '#', 1, LEVEL ) + 1,

INSTR(comb, '#', 1, LEVEL+1) -

INSTR(comb, '#', 1, LEVEL) -1) comb, rn

FROM

(SELECT comb, rn

FROM

(SELECT sys_connect_by_path(n, '#')||'#' comb, rownum rn

FROM

(SELECT chr(ascii('A')+level-1) n

FROM dual CONNECT BY LEVEL <=:input)

CONNECT BY n > PRIOR n

)

)

CONNECT BY PRIOR comb = comb AND PRIOR rn = rn

AND INSTR (comb, '#', 1, LEVEL+1) > 0

AND PRIOR dbms_random.string ('p', 10) IS NOT NULL

)

GROUP BY rn

ORDER BY rn;

 

COL_1 COL_2 COL_3

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

A

A B

A B C

A C

B

B C

C

 

Here I will display the letters as # to make it more generic.

 

SELECT

REGEXP_REPLACE(MAX(CASE WHEN comb = 'A'

THEN comb END), '[[:alpha:]]','#') AS COL_1,

REGEXP_REPLACE(MAX(CASE WHEN comb = 'B'

THEN comb END), '[[:alpha:]]','#') AS COL_2,

REGEXP_REPLACE(MAX(CASE WHEN comb = 'C'

THEN comb END), '[[:alpha:]]','#') AS COL_3

FROM

(SELECT

SUBSTR(comb,

INSTR(comb, '#', 1, LEVEL ) + 1,

INSTR(comb, '#', 1, LEVEL+1) -

INSTR(comb, '#', 1, LEVEL) -1 ) comb, rn

FROM

(SELECT comb, rn

FROM

(SELECT sys_connect_by_path(n, '#')||'#' comb, rownum rn

FROM

(SELECT chr(ascii('A')+level-1) n

FROM dual CONNECT BY LEVEL <=3)

CONNECT BY n > PRIOR n

)

)

CONNECT BY PRIOR comb = comb AND PRIOR rn = rn

AND INSTR (comb, '#', 1, LEVEL+1) > 0

AND PRIOR dbms_random.string ('p', 10) IS NOT NULL

)

GROUP BY rn

ORDER BY rn;

 

COL_1 COL_2 COL_3

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

#

# #

# # #

# #

#

# #

#

 

7 rows selected.

 

SQL> spool off;


 

Back to top

Back to index of questions