How to display combinations using the table format in a SQL statement
February 14th, 2007 By Frank Zhou
The following SQL pattern can be used to display all the combinations for a predetermined number of columns using the table format.
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.
July 5th, 2010 at 12:55 pm
variable input number exec :input := 4 SELECT CASE WHEN BITAND(ROWNUM,1)>0 THEN '#' END ,CASE WHEN BITAND(ROWNUM,2)>0 THEN '#' END ,CASE WHEN BITAND(ROWNUM,4)>0 THEN '#' END ,CASE WHEN BITAND(ROWNUM,8)>0 THEN '#' END FROM DUAL CONNECT BY ROWNUM<=POWER(2,:input)-1; C C C C - - - - # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # 15 rows selected.