Hello,
I'm attempting to generate rows based off of a part numbers quantity. I've looked all around at several techniques for row generation, but have been unsuccessful in accomplishing this. The current method it seems is pulling a permutation of all possible combinations, and the result quickly amounts to millions/billions of records... which is not the answer. Anyone out there that could help with writing a query to do this?
What I'm trying to achieve is if a part qty has 5, then I want to display 5 records of qty 1. If It's qty 12, then 12 records of qty 1. So on and so forth.
Operating off Oracle 10.2.0.4
Sample data:
drop table test;
create table test (part varchar2(20), qty number);
insert into test values ('WIDGET', 12);
insert into test values ('FOO', 5);
insert into test values ('THING', 3);
insert into test values ('WAD', 8);
commit;
--this query doesn't work... I think it's getting all possible permutations
--select part, 1
--from test
--connect by level <= qty
--what I want to see....
part qty
------ ----
WIDGET 1
WIDGET 1
WIDGET 1
WIDGET 1
WIDGET 1
WIDGET 1
WIDGET 1
WIDGET 1
WIDGET 1
WIDGET 1
WIDGET 1
WIDGET 1
FOO 1
FOO 1
FOO 1
FOO 1
FOO 1
THING 1
THING 1
THING 1
WAD 1
WAD 1
WAD 1
WAD 1
WAD 1
WAD 1
WAD 1
WAD 1