Showing posts with label replicate rows. Show all posts
Showing posts with label replicate rows. Show all posts

Tuesday, October 23, 2007

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

Labels