|
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? |
|
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
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;