Split the Row into multiple rows in SQL using Oracle

Question

I have the data in the following format in table.

ORDER_ID   PRODUCT_ID   QUANTITY
O1          A1           3

I need to write the sql query to divide the data into following format:

ORDER_ID   PRODUCT_ID   QUANTITY
O1          A1           1
O1          A1           1
O1          A1           1

Query should split the data based on the Value in quantity column.

Thanks

TechDo · Accepted Answer · 2013-09-20 12:18:51Z

3

Please try:

SELECT
  ORDER_ID, 
  PRODUCT_ID, 
  1 QUANTITY 
from 
  dual LEFT JOIN YourTable T on 1=1
connect by level <= T.Quantity

answered Sep 20, 2013 at 12:18

TechDo

18.7k3 gold badges55 silver badges64 bronze badges

Sign up to request clarification or add additional context in comments.

1 Answer 1