Create multiple rows based on 1 column

Question

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

Smells like an XY problem. Can you explain why you need to do this? — Joe Stefanelli
– Joe Stefanelli, Commented Dec 14, 2018 at 18:09
Which DBMS product are you using? "SQL" is just a query language, not the name of a specific database product. Please add the tag for the database product you are using postgresql, oracle, db2, sql-server, ... — user330315
– user330315, Commented Dec 14, 2018 at 18:12
join to a table with numbers 1..n in it using on n between 1 and quantity — dnoeth
– dnoeth, Commented Dec 14, 2018 at 18:14

sgeddes · Accepted Answer · 2018-12-14 18:18:57Z

4

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

Online Demo

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.

answered Dec 14, 2018 at 18:18

sgeddes

62.9k7 gold badges67 silver badges85 bronze badges

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

Comments

dtsellos03 · Accepted Answer · 2018-12-14 18:12:30Z

0

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!

answered Dec 14, 2018 at 18:12

dtsellos03

435 bronze badges

Comments

Derviş Kayımbaşıoğlu · Accepted Answer · 2018-12-14 18:27:05Z

0

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));

answered Dec 14, 2018 at 18:27

Derviş Kayımbaşıoğlu

30.9k4 gold badges55 silver badges79 bronze badges

Collectives™ on Stack Overflow

Create multiple rows based on 1 column

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related