Creating sequence in SQL with different length

Question

I have a table with the customer identifier as PK and his time to maturity in months:

Customer |  Maturity
---------+-----------
1             80
2             60
3             52
4             105

I want to create a table which will have customer identifier and the maturity will be defined as sequence of number with the increment + 1:

Customer |  Maturity
---------+------------
1             1
1             2
1            ....
1             80
2             1
2             2
2            ...
2             60

I don't know whether I should use a sequence or the cross join or how to solve this problem.

Possible duplicate of [Repeat Rows N Times According to Column Value](https://stackoverflow.com/questions/33327837/repeat-rows-n-times-according-to-column-value) — HABO, Oct 06 '17 at 13:20

Tim Biegeleisen · Answer 1 · 2017-10-06T09:14:36.727

2

You can try joining your current table to a sequence table to generate the maturity ranges you want.

WITH cte AS (
    SELECT 1 AS seq
    UNION ALL
    SELECT seq + 1
    FROM cte
    WHERE seq < 500
)

SELECT
    t1.Customer,
    t2.seq AS Maturity
FROM yourTable t1
INNER JOIN cte t2
    ON t2.seq <= t1.Maturity
ORDER BY
    t1.Customer,
    t2.seq
OPTION (MAXRECURSION 0);

Demo here:

Rextester

edited Oct 06 '17 at 09:14

answered Oct 06 '17 at 08:57

Tim Biegeleisen

502,043
27
286
360

score 2 · Answer 2 · answered Oct 06 '17 at 09:11

one way is to use recursive CTE.

; with cte as
(
    select  Customer, M = 1, Maturity
    from    yourtable
    union all
    select  Customer, M = M + 1, Maturity
    from    yourtable
    where   M < Maturity
)
select  *
from    cte
option (MAXRECURSION  0)

score 1 · Answer 3 · answered Oct 06 '17 at 08:55

you can try query like below

create table t (Customer int,  Maturity int)
insert into t values 
(1,80)
,(2,60)
,(3,52)
,(4,105);

select Customer, r from 
t cross join
(select top (select max(maturity) from t)
row_number() over( order by (select NULL)) r 
from sys.objects s1 cross join sys.objects s2) k
where r<=Maturity
order by Customer asc,r asc

see live demo

score 0 · Answer 4 · answered Oct 06 '17 at 09:09

You can try the below. Created two temporary tables to represent your tables in below example. You need to replace them with you table names and drop the first three lines.

declare @Customer table (Customer int, Maturity int)
declare @NewTable table (Customer int, Maturity int)
insert @Customer select 1, 80

declare @x int = 0
declare @iterations table (x int)
while @x <= (select max(Maturity) from @Customer)
begin
    set @x += 1
    insert @iterations select @x
end

insert @NewTable
select  c.Customer, i.x from @Customer c left join @iterations i on i.x <= c.Maturity

select * from @NewTable

score 0 · Answer 5 · answered Oct 06 '17 at 10:58

0

Late answer, but another option is an ad-hoc tally table in concert with a CROSS APPLY

Example

Select A.customer 
      ,Maturity = B.N
 From  YourTable A
 Cross Apply (
                Select Top (A.Maturity) N=Row_Number() Over (Order By (Select NULL)) 
                  From master..spt_values n1
             ) B

answered Oct 06 '17 at 10:58

John Cappelletti

79,615
7
44
66

i like your answer best,becasue your r using simple number table and if number table is permanent then query is more simple. – KumarHarsh Oct 06 '17 at 11:54
@KumarHarsh actually any table of appropriate size will do. spt_values has 2,500 records. If you need more, you can use master..spt_values n1, master..spt_values n2 which has up to 6,365,529 records – John Cappelletti Oct 06 '17 at 12:17

Creating sequence in SQL with different length

5 Answers5

Rextester