Repeat Records multiple times based on some column

Oracle Query to Repeat a Number n Times

Below scenarios using oracle hierarchy query’s (ex:- START WITH and CONNECT BY, Level etc.,)

Scenario 1:-
TABLE TBl

VALUE	CNT
foo	1
bar	3
baz	2

Expected Output:-  

VALUE	CNT	NDX
foo	1	1
baz	2	1
baz	2	2
bar	3	1
bar	3	2
bar	3	3

Solution for Netezza:- use the below method

 

WITH tbl_populate(value, cnt, ndx) 
     AS (SELECT value, 
                cnt, 
                1 
         FROM   tbl 
         UNION ALL 
         SELECT t.value, 
                t.cnt, 
                tp.ndx + 1 
         FROM   tbl t 
                JOIN tbl_populate tp 
                  ON tp.value = t.value 
                     AND tp.ndx + 1 <= t.cnt) 
SELECT * 
FROM   tbl_populate 
ORDER  BY cnt, 
          ndx 

Solution for Oracle:- 

SELECT value,
cnt
FROM tbl p,
(SELECT ROWNUM repeat
FROM dual
CONNECT BY LEVEL <= (SELECT Max(cnt)
FROM tbl))r
WHERE p.tbl >= r.repeat;Scenario 2:-
I have name like INDIA in a column. I want display 1st line A, 2nd line N, 3rd line D, 4th line I, 5th line A in oracle data
base?

Solution:-
SELECT Substr(‘INDIA’, LEVEL, 1) AS INDIA
FROM dual
CONNECT BY LEVEL <= Length(‘INDIA’);

Scenario 3:-

Source data

ID NAME
101 X,Y,Z
102 XXX,Y,ZZ,AAAAA,B,CCC,D,E,F,GGG

Desired output:-
101 X
101 Y
101 Z
102 XXX
102 Y
102 ZZ
102 AAAAA
102 B
102 CCC
102 D till the end…..

Oracle Solution 1:-

SELECT NAME ,
regexp_substr(NAME, ‘[^,]+’, 1, LEVEL) AS single_element ,
LEVEL AS element_no
FROM (
SELECT ROWNUM AS id1 ,
NAME
FROM TABLE )
CONNECT BY instr(NAME, ‘,’, 1, LEVEL-1) > 0
AND id1 = PRIOR id
AND PRIOR dbms_random.value IS NOT NULL;

Oracle Solution 2:-

SELECT NAME AS original_string ,
extractvalue( xt.column_value, ‘element’ ) AS single_element
FROM t ,
TABLE( xmlsequence( extract( xmltype( ”
|| replace( t.str, ‘,’, ” )
|| ” ), ‘/coll/element’ ) ) ) xt;