|
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? |
|
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;