1
FIRST_COL VAL_COL
A          2 
B          3
C         4

I need output as below.

FIRST_COL VAL_COL
A          2
A          2
B          3
B          3
B          3
C          4
C          4
C          4
C          4

example: my original table contains a=2 but I need in output as a=2 two times same for of b=3 three times c=4 four times

2 Answers 2

5

If you are using Oracle 12c or higher, you can CROSS APPLY with a CONNECT BY LEVEL query generating the numbers from 1 to VAL_COL. Something like this (I don't have an instance ready and SQLFiddle uses 11g, which doesn't support this):

SELECT FIRST_COL, VAL_COL
FROM table1
CROSS APPLY (
  SELECT LEVEL
    FROM DUAL  
    CONNECT BY LEVEL <= table1.VAL_COL
);

If you are on 11g or higher, you can use Recursive Refactored Subquery.

with data as (
  -- example data
  select 'A' FIRST_COL, 2 VAL_COL from dual union all
  select 'B' FIRST_COL, 3 VAL_COL from dual union all
  select 'C' FIRST_COL, 4 VAL_COL from dual
), counter(first_col,val_col, iterator) as (
  select first_col, val_col, val_col
  from data
  union all
  select first_col, val_col, iterator - 1
  from counter
  where iterator - 1 > 0
)
select first_col, val_col
from counter
order by first_col;

Prior to 11g, you would need to use a pipeline function

1
  • LiveSQL.oracle.com is 18c. (requires registration, but is free). I believe Oracle will update it as never versions of their database comes out. Sep 14, 2018 at 19:29
2

Yet another option:

SQL> with test (first_col, val_col) as
  2    (select 'A', 2 from dual union all
  3     select 'B', 3 from dual union all
  4     select 'C', 4 from dual
  5    )
  6  select t.first_col
  7  from test t,
  8      table(cast(multiset(select level from dual
  9                          connect by level <= t.val_col
 10                         ) as sys.odcinumberlist))
 11  order by t.first_col;

FIRST_COL
--------------------
A
A
B
B
B
C
C
C
C

9 rows selected.

SQL>

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.