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;