DROP TABLE TEST PURGE;
CREATE TABLE TEST (po_number VARCHAR2 (+), Product_Name VARCHAR2 (+), quantity number);
INSERT into TEST VALUES (' CJ201300048 ', ' tp-link network switch TP-1024DT ', 2);
INSERT into TEST VALUES (' CJ201300049 ', ' 4-way/Trenton 600-wire Hemisphere/Seagate HDD 500G ', 1);
INSERT into TEST VALUES (' CJ201300050 ', ' TP link wireless router DAP-1353 ', 2);
INSERT into TEST VALUES (' CJ201300060 ', ' ke mi electronic Access mf850d ', 1);
Commit
With a as (select ROWNUM rn from dual CONNECT by ROWNUM <= (select MAX (t.quantity) from TEST t))
SELECT t1.po_number,t1.product_name,1 quantity from TEST T1 left JOIN A on t1.quantity >= a.rn
Sql> with A as (select ROWNUM rn from dual CONNECT by ROWNUM <= (select MAX (t.quantity) from TEST t))
2 SELECT t1.po_number,t1.product_name,1 quantity from TEST T1 left JOIN A on t1.quantity >= a.rn
3;
Po_number Product_Name QUANTITY
---------------- ---------------------------------------------------------------- ----------
CJ201300050 TP Link Wireless Router DAP-1353 1
CJ201300050 TP Link Wireless Router DAP-1353 1
CJ201300048 tp-link Network Switch TP-1024DT 1
CJ201300048 tp-link Network Switch TP-1024DT 1
CJ201300060 Electronic access Control mf850d 1
CJ201300049 4-Way/Trenton 600-wire Dome/Seagate hard Drive 500G 1
6 Rows selected
Sql>
Oracle splits a row into multiple lines