Write a SQL Query to explode a single source record into multiple records based on the quantity

 

 

 

 

CREATE TABLE ORDER_DETAILS(

ORDER_DATE DATE,

ORDERS INTEGER

);INSERT INTO ORDER_DETAILS VALUES ('2021-01-01',2);

INSERT INTO ORDER_DETAILS VALUES ('2021-02-01',1);

INSERT INTO ORDER_DETAILS VALUES ('2021-03-01',3);

INSERT INTO ORDER_DETAILS VALUES ('2021-04-01',4);

commit;

 

WITH TEMP(ORDER_DATE,ORDERS) AS(

--Anchor Block

SELECT ORDER_DATE, ORDERS FROM ORDER_DETAILSUNION ALL--Recursive Block

SELECT ORDER_DATE, orders-1 FROM TEMP WHERE ORDERS > 1

)

SELECT ORDER_DATE, ORDERS FROM TEMP ORDER BY ORDER_DATE;

 

WITH TEMP(ORDER_DATE,ORDERS) AS(

--Anchor Block

SELECT ORDER_DATE, ORDERS FROM ORDER_DETAILSUNION ALL--Recursive Block

SELECT ORDER_DATE, orders-1 FROM TEMP WHERE ORDERS > 1

)

SELECT ORDER_DATE+(ORDERS-1) AS ORDER_DATE FROM TEMP ORDER BY ORDER_DATE;