Postgres, split one row into multiple rows with conditions

Question

I have a table order_item like this:

  | sku | quantity | subtotal |
  |-----|----------|----------|
  |  A  |        1 |   25.00  |
  |  B  |        2 |   40.00  |
  |  C  |        3 |   45.00  |

Is there any way to select "unit order_item" rows from order_item with result like:

  | sku | unit_price |
  |-----|------------|
  |  A  |   25.00    |
  |  B  |   20.00    |
  |  B  |   20.00    |
  |  C  |   15.00    |
  |  C  |   15.00    |
  |  C  |   15.00    |

where the result row counts of a sku is the quantity in order_item and the unit_price is (subtotal/quantity) ?

user330315 · Accepted Answer · 2016-08-29 13:33:58Z

2

You can use generate_series() for that:

select oi.sku, oi.subtotal / oi.quantity as unit_price
from order_item oi
  cross join lateral generate_series(1,oi.quantity)
order by oi.sku;

answered Aug 29, 2016 at 13:33

user330315

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

Collectives™ on Stack Overflow

Postgres, split one row into multiple rows with conditions

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related