CODEWITHSUNDEEP
sqlamazon-web-servicesprestoamazon-athena
Gowsikan
Gowsikan

Reputation: 5691

AWS Athena create rows based on a column value

I have a table showing simple order details as follows

+---------+---------+---------+
| Order_id| Item_id |Quantity |
+---------+---------+---------+
| 10001   | 1       |   4     |
+---------+---------+---------+

Now I need to show each quantity as a line item as follows.

+---------+---------+---------+---------+
| Order_id| Item_id | Line_id |Quantity |
+---------+---------+---------+---------+
| 10001   | 1       | 1       |   1     |
+---------+---------+---------+---------+ 
| 10001   | 1       | 2       |   1     |
+---------+---------+---------+---------+ 
| 10001   | 1       | 3       |   1     |
+---------+---------+---------+---------+ 
| 10001   | 1       | 4       |   1     |
+---------+---------+---------+---------+

Can any one help me how can I write query to achieve this?

Upvotes: 1

Views: 1251

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269493

You can use unnest on an array to get the value you want:

select od.order_id, od.item_id, u.line_number, u.quantity
from order_details od cross join
     unnest(repeat(1, od.quantity)) with ordinality u(quantity, line_number)

Upvotes: 1

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520908

We can try using a recursive CTE here:

WITH cte AS (Order_id, Item_id, Line_id, Amount) AS (
    SELECT Order_id, Item_id, Quantity, 1
    FROM yourTable
    UNION ALL
    SELECT Order_id, Item_id, Line_id - 1, 1
    FROM cte
    WHERE Line_id - 1 >= 1
)

SELECT Order_id, Item_id, Line_id, Quantity
FROM cte
ORDER BY Order_id, Item_id, Line_id;

The logic in the CTE above is that we form the base case of the recursion using the following tuple from your source table:

(10001, 1, 4, 1)

That is, we start counting for the Line_id from 4, decreasing by one in each step of the recursion. At the end of the recursive case, we hit a Line_id value of 1.

Upvotes: 1

Related Questions