CODEWITHSUNDEEP
sqloracle-database
Blue
Blue

Reputation: 179

How to duplicate the rows of a table based on count

I have a frequency table like this in an Oracle database where N is the number of each occurrence:

weight | N
12     | 3
34     | 2
56     | 1

Is it possible to multiply the number of each row by the number of occurences to get a table like this:

weight | N
12     | 1
12     | 1
12     | 1
34     | 1
34     | 1
56     | 1

Thank you,

Upvotes: 0

Views: 41

Answers (2)

Ponder Stibbons
Ponder Stibbons

Reputation: 14848

You can use number generator, recursive query, xmltable. Many options. For example:

with t as (select weight, '1 to '||n list from frequency)
select weight, 1 from t, xmltable(list)

or

with r(weight, n) as (select weight, n from frequency union all
                      select weight, n-1 from r where n-1 > 0)
select weight, 1 from r order by weight

dbfiddle

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269803

One simple method is a recursive CTE:

with recursive cte as (
      select weight, n
      from t
      union all 
      select weight, n - 1
      from t
      where n > 1
     )
select weight, 1 as n
from cte;

Note that although recursive CTEs are part of the standard, there are variations in the syntax, so your database might be a little different.

EDIT:

In older versions of Oracle, you can generate the numbers you need and then generate the data:

with n as (select level as n
           from (select max(n) as n from t) n
           connect by level <= n
          )
select t.weight, 1 as n
from t join
     n
     on n.n <= t.n;

New versions of Oracle support recursive CTEs.

Upvotes: 0

Related Questions