The Oracle (tm) Users' Co-Operative FAQ

How do I repeat each row multiple times based on a column value in that row in a SQL Statement?


Author's name: Frank Zhou

Author's Email:zhou328@comcast.net

Date written: 28th December 2005

Updated written: 6th January 2007

Oracle version(s): 9.2

How do I repeat each row multiple times based on a table column value in that row in a SQL Statement?

Back to index of questions


The following SQL query pattern can be used to repeat each row multiple times based on a column value in that row. This pattern is restricted by the maximum length of varchar2 in the SQL Query

Here is the testing table:

 

SQL> select chr(ascii('A')+level-1) id, level num from dual connect by level <=4

  2  /

 

I        NUM                                                                   

- ----------                                                                   

A          1                                                                    

B          2                                                                   

C          3                                                                   

D          4      

 

SELECT id, num

FROM

(

 SELECT id, num, rpad('#', abs(num)+1, '#') str

   FROM

      (SELECT chr(ascii('A')+level-1) id, level num

         FROM dual CONNECT BY level <=4) YourTable

)

CONNECT BY PRIOR id = id

AND INSTR (str, '#', 1, LEVEL+1) > 0

AND PRIOR dbms_random.string ('p', 10) IS NOT NULL

 

I        NUM                                                                   

- ----------                                                                   

A          1                                                                   

B          2                                                                    

B          2                                                                   

C          3                                                                   

C          3                                                                    

C          3                                                                   

D          4                                                                   

D          4                                                                    

D          4                                                                   

D          4                                                                   

 

10 rows selected.

 

SQL> spool off

 


Updated SQL Pattern January 2007

This updated SQL pattern can remove the restriction (the maximum length of varchar2 in the SQL Query) in the previous query.

SELECT id, num

FROM

(

 SELECT id, num

   FROM

      (SELECT chr(ascii('A')+level-1) id, level num

         FROM dual CONNECT BY level <=4) YourTable

)

CONNECT BY PRIOR id = id

AND level < ABS(num) +1

AND PRIOR dbms_random.string ('p', 10) IS NOT NULL

 

I        NUM                                                                   

- ----------                                                                    

A          1                                                                   

B          2                                                                   

B          2                                                                    

C          3                                                                   

C          3                                                                   

C          3                                                                    

D          4                                                                   

D          4                                                                   

D          4                                                                    

D          4                                                                   

 

10 rows selected.

 

SQL> spool off;

 

 


 

 

 

 

Back to top

Back to index of questions