SELECT as many rows as indicated by column value

This select statement will return as many rows as are indicated by the value of a column. For example, if the column value is 7, then 7 copies of that row will be returned.


CREATE TABLE S
(
RNUM NUMBER,
TPART CHAR(10),
SQ NUMBER
)
;

Insert into S
(RNUM, TPART, SQ)
Values
(101, 'TEST_PART1', 1);
Insert into S
(RNUM, TPART, SQ)
Values
(102, 'TEST_PART2', 2);
Insert into S
(RNUM, TPART, SQ)
Values
(103, 'TEST_PART3', 3);
Insert into S
(RNUM, TPART, SQ)
Values
(105, 'TEST_PART5', 5);
COMMIT;

SELECT rnum, tpart, sq
FROM s s_alias, TABLE(SELECT COLLECT(dummy)
FROM DUAL
CONNECT BY LEVEL <= s_alias.sq);