How to return multiple rows from a field

[fej7yhdoq7]

I got a table that looks like this:
ID Copies
== ======
1 2
2 3
3 1
I'm searching for a way to list all rows the number of times of the field 'Copies', the result would look like this:
ID
==
1
1
2
2
2
3

Thanx

 

[fmorel]

With pure SQL:
You can create a table called "numbers" with 1 column: n
create table numbers (n number);

then you insert numbers in it:
insert into numbers (1);
insert into numbers (2);
...
insert into numbers ; n equals the max value of "Copies".

Then your select will be:
select id from yourtable,numbers
where Copies>=n
order by id;

... There are also very simple ways to do that using PL/SQL

 

[sem]

You need some kind of pivot table. As it's not available in 8i you may use all_objects or some other sufficiently large table:

select src.id, a.rn
from src,
(select rownum rn from all_objects
where rownum <=(select max(copies) from src) /*this speeds up query if max(copies) is small*/
) a
where rn <= src.copies
order by 1,2
/

I suppose that your source table is SRC.