CODEWITHSUNDEEP
sql
Kd5490
Kd5490

Reputation: 31

Create multiple rows based on 1 column

I currently have a table with a quantity in it. 

ID       Code            Quantity
1        A               1
2        B               3
3        C               2
4        D               1

Is there anyway to write a sql statement that would get me

ID       Code            Quantity
1        A               1
2        B               1
2        B               1
2        B               1
3        C               1
3        C               1
4        D               1

I need to break out the quantity and have that many number of rows

Thanks

Upvotes: 0

Views: 2713

Answers (3)

Derviş Kayımbaşıoğlu
Derviş Kayımbaşıoğlu

Reputation: 30545

You can use connect by statement to cross join tables in order to get your desired output. check my solution it works pretty robust.

select 
  "ID", 
  "Code", 
  1 QUANTITY 
from Table1, table(cast(multiset
(select level from dual 
 connect by level <= Table1."Quantity") as  sys.OdciNumberList));

Upvotes: 0

sgeddes
sgeddes

Reputation: 62831

Here's one option using a numbers table to join to:

with numberstable as (
    select 1 AS Number
    union all
    select Number + 1 from numberstable where Number<100
    )
select t.id, t.code, 1 
from yourtable t 
    join numberstable n on t.quantity >= n.number 
order by t.id

Please note, depending on which database you are using, this may not be the correct approach to creating the numbers table. This works in most databases supporting common table expressions. But the key to the answer is the join and the on criteria.

Upvotes: 4

dtsellos03
dtsellos03

Reputation: 43

One way would be to generate an array with X elements (where X is the quantity). So for rows

ID       Code            Quantity
1        A               1
2        B               3
3        C               2

you would get

ID       Code            Quantity   ArrayVar
1        A               1          [1]
2        B               3          [1,2,3]
3        C               2          [2]

using a sequence function (e.g, in PrestoDB, sequence(start, stop) -> array(bigint))

Then, unnest the array, so for each ID, you get a X rows, and set the quantity to 1. Not sure what SQL distribution you're using, but this should work!

Upvotes: 0

Related Questions