### Multiply rows

F

Few queries you can use for multiplying rows.

### Multiply Rows Evenly

WITH d AS (
SELECT
REGEXP_SUBSTR('A|B|C|D|E', '[^|]+', 1, LEVEL)   AS combo,
LEVEL                                           AS lvl
FROM DUAL
CONNECT BY LEVEL <= REGEXP_COUNT('A|B|C|D|E', '[^|]+[|]') + 1
)
SELECT
LTRIM(SYS_CONNECT_BY_PATH(d.combo, '|'), '|') AS combo,
--
CASE WHEN INSTR(SYS_CONNECT_BY_PATH(d.combo, '|'), 'A') > 0 THEN 'Y' END AS a,
CASE WHEN INSTR(SYS_CONNECT_BY_PATH(d.combo, '|'), 'B') > 0 THEN 'Y' END AS b,
CASE WHEN INSTR(SYS_CONNECT_BY_PATH(d.combo, '|'), 'C') > 0 THEN 'Y' END AS c,
CASE WHEN INSTR(SYS_CONNECT_BY_PATH(d.combo, '|'), 'D') > 0 THEN 'Y' END AS d,
CASE WHEN INSTR(SYS_CONNECT_BY_PATH(d.combo, '|'), 'E') > 0 THEN 'Y' END AS e
FROM d
CONNECT BY NOCYCLE PRIOR d.lvl < d.lvl
ORDER BY 1;

### Multiply Rows by Specific Number

WITH x AS (
SELECT 'A' AS name, 2 AS rows_ FROM DUAL UNION ALL
SELECT 'B' AS name, 4 AS rows_ FROM DUAL UNION ALL
SELECT 'C' AS name, 3 AS rows_ FROM DUAL UNION ALL
SELECT 'D' AS name, 1 AS rows_ FROM DUAL UNION ALL
SELECT 'E' AS name, 0 AS rows_ FROM DUAL
)
SELECT d.name, d.row#
FROM (
SELECT
x.*,
ROW_NUMBER() OVER (PARTITION BY x.name ORDER BY ROWNUM) AS row#
FROM x
CONNECT BY LEVEL <= x.rows_
) d
WHERE d.row# <= d.rows_
ORDER BY 1, 2;

### Combinations (when order matters)

WITH d AS (
SELECT
REGEXP_SUBSTR('A|B|C|D|E', '[^|]+', 1, LEVEL)   AS combo,
LEVEL                                           AS lvl
FROM DUAL
CONNECT BY LEVEL <= REGEXP_COUNT('A|B|C|D|E', '[^|]+[|]') + 1
)
SELECT LTRIM(SYS_CONNECT_BY_PATH(d.combo, '|'), '|') AS combo
FROM d
CONNECT BY NOCYCLE PRIOR d.lvl != d.lvl
ORDER BY 1;

### Permutations (when order does not matters)

WITH d AS (
SELECT
REGEXP_SUBSTR('A|B|C|D|E', '[^|]+', 1, LEVEL)   AS combo,
LEVEL                                           AS lvl
FROM DUAL
CONNECT BY LEVEL <= REGEXP_COUNT('A|B|C|D|E', '[^|]+[|]') + 1
)
SELECT
LTRIM(SYS_CONNECT_BY_PATH(d.combo, '|'), '|') AS combo,
--
CASE WHEN INSTR(SYS_CONNECT_BY_PATH(d.combo, '|'), 'A') > 0 THEN 'Y' END AS a,
CASE WHEN INSTR(SYS_CONNECT_BY_PATH(d.combo, '|'), 'B') > 0 THEN 'Y' END AS b,
CASE WHEN INSTR(SYS_CONNECT_BY_PATH(d.combo, '|'), 'C') > 0 THEN 'Y' END AS c,
CASE WHEN INSTR(SYS_CONNECT_BY_PATH(d.combo, '|'), 'D') > 0 THEN 'Y' END AS d,
CASE WHEN INSTR(SYS_CONNECT_BY_PATH(d.combo, '|'), 'E') > 0 THEN 'Y' END AS e
FROM d
CONNECT BY NOCYCLE PRIOR d.lvl < d.lvl
ORDER BY 1;