Split one Value from column into multiple row SQL

Question

I have a problem, and i want to convert row in table A as the data in table B, can anyone help me with this case in SQL?

Answer 1

I would use a tally table.

-- create a tally table
select top 100 identity(int,1,1) as n
into  dbo.tally
from  master.sys.all_columns ac1
        cross join master.sys.all_columns ac2

alter table dbo.tally
  add constraint pk_tally_n
      primary key clustered (n) with fillfactor = 100

-- your table
create table A (id int, paid_at datetime, client_id int, price int, billing_cycle int)
insert into A values(234, '2019-01-01', 3543, 3600000,12)


-- the soution
select  a.id
      , dateadd(month, n - 1, paid_at) paid_at
      , a.client_id
      , price / a.billing_cycle price
      , 1 billing_cycle
from    A a
        join dbo.tally t on t.n <= a.billing_cycle
order by 1, 2

Answer 2

I think a recursive CTE is a simple method:

with recursive cte as (
      select id, paid_at, paid_at as paid_for, client_id, price / billing_cycle as price, billing_cycle, 1 as n
      from a
      union all
      select id, paid_at, paid_for + interval 1 month, price, billing_cycle, n + 1
      from cte
      where n < billing_cycle
     )
select id, paid_at, paid_for, client_id, price, 1 as billing_cycle
from cte;