SQL: How to add multiple records in table based on variable amount which differs per value-CodePudding

I have a table A like this:

SKU	Value
First	5
Second	3

I want to add records to a table B, based on the records in table A. Table B should be looking like this:

SKU	Number
First	1
First	2
First	3
First	4
First	5
Second	1
Second	2
Second	3

How to do this using SQL INSERT INTO statement?

CodePudding user response：

You can use a recursive CTE to expand the rows. For example:

with
r as (
  select sku, value, 1 as cur from a
 union all
  select sku, value, cur   1 from r where cur < value
)
insert into b (sku, n) select sku, cur from r;

Result:

 sku     n 
 ------- - 
 First   1 
 Second  1 
 Second  2 
 Second  3 
 First   2 
 First   3 
 First   4 
 First   5

See running example at db<>fiddle.

CodePudding user response：

You need a table with all number_list and do join:

select sku, number_list
  from (
        select 1 as number_list
         union all
        select 2 as number_list
         union all
        select 3 as number_list
         union all
        select 4 as number_list
         union all
        select 5 as number_list
         union all
        select 6 as number_list
         union all
        select 7 as number_list
         union all
        select 8 as number_list
         union all
        select 9 as number_list
         union all
        select 10 as number_list
        )nb
  join (
        select 'First' as sku, 5 as val
         union
           all
        select 'Second' as sku, 3 as val
       )sk
    on nb.number_list <= sk.val
 order by sku, number_list asc