Repeat Records multiple times based on some column

Posted: October 13, 2014 in Netezza, Oracle

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;

Advertisements
Comments
  1. Hi Vamshi,

    I tried using this with caluse and it doesnot work with Union all option. Same query i tried running in my system and it did not work. Is it that i am missing something?

    Thanks,
    Jahnavi

    Like

  2. NZ_Learner says:

    The netezza solution doesnt work… it says the tbl_populate not found… pls check it.

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s