How to duplicate values based on an variable

Question

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.

Please present a minimal reproducible example

EdStevens
– EdStevens

2022-01-26 20:45:26 +00:00
Commented Jan 26, 2022 at 20:45 — EdStevens
– EdStevens, Commented Jan 26, 2022 at 20:45

MT0 · Accepted Answer · 2022-01-26 20:52:09Z

1

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

db<>fiddle here

answered Jan 26, 2022 at 20:52

MT0

173k12 gold badges70 silver badges136 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to duplicate values based on an variable

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related