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

3 comments:

Rob Wolfe said...

This is a really slick little trick Mike. Thanks for sharing it!

moleboy said...

I'm a little confused. Why would you do it this way?
If I wanted 5 rows from my Names table, I'd just
Select * from names where rownum < = 5

Unknown said...

Hi,
If I have data like:

Mike,2
Fred,3

I want the output to be

Mike
Mike
Fred
Fred
Fred

In other words, the number of rows to be produced is indicated by the column value, in this case, 2 and 3.
Regards,
Mike

Labels