How to duplicate values based on an variable-CodePudding

So for background, my company is running a contest. The number of units a customer has purchased determines how many entries they get into the contest.

The data partner we have to provide the list to wants a list of all account numbers with duplicated values to determine how many entries a person has.

So basically, I have a dataset that has ACCOUNT_NBR and UNITS. If a Customer 12345 has 6 units, I want their account number to be in the final dataset 6 times.

Sorry if this doesn't make sense, will happily clarify if needed.

CodePudding user response：

From Oracle 12, you can use:

SELECT account_nbr
FROM   table_name t
       CROSS JOIN LATERAL (
         SELECT LEVEL FROM DUAL CONNECT BY LEVEL <= t.units
       )

Before Oracle 12, you can use:

SELECT account_nbr
FROM   table_name t
       CROSS JOIN TABLE(
         CAST(
           MULTISET(
             SELECT LEVEL FROM DUAL CONNECT BY LEVEL <= t.units
           ) AS SYS.ODCINUMBERLIST
         )
       )

Which, for the sample data:

CREATE TABLE table_name (account_nbr, units) AS
SELECT 1111, 3 FROM DUAL UNION ALL
SELECT 2222, 1 FROM DUAL UNION ALL
SELECT 3333, 4 FROM DUAL UNION ALL
SELECT 4444, 2 FROM DUAL;

Both output:

ACCOUNT_NBR

1111

1111

1111

2222

3333

3333

3333

3333

4444

4444

ACCOUNT_NBR
1111
1111
1111
2222
3333
3333
3333
3333
4444
4444

db<>fiddle here