You have an orders table with order details. You have to ungroup the data based on the order quantity.
The amount column in the output should be an amount of a single quantity. Assume that the product's amount will be the same regardless of the quantity ordered.
Write an SQL query to ungroup the orders table.
CREATE TABLE order_details(
INSERT INTO order_detail (order_id,product,quantity,amount) VALUES
I have implemented the solution using Number Sequence and CROSS JOIN. First I have Generated the Number Sequence using RECURSIVE CTE and then CROSS JOIN this Number Sequence table with the Orders table based on the quantity in the Orders table.
DECLARE @max_num int = (SELECT max(quantity) FROM order_details);
;WITH numbers (num) as
( SELECT 1 as num
WHERE num< @max_num
SELECT order_id, product,
1 AS quantity, CAST(amount / quantity as DECIMAL(18,2)) AS amount
CROSS JOIN numbers
WHERE quantity>= num
ORDER BY order_id, product;
Thanks for reading. I hope you enjoyed this fun little case study - it was fun for me to create!. You can also share your approach to solve this problem.