SQL: creating a sequential list of numbers from different starting points

Question

I am stuck in this SQL issue.

I have a column that is a list of starting points ( prevdoc ) and an afther column that shows how many consecutive numbers I need after the starting point ( exdiff ).

For example, here are the first few lines:

 prevdoc | exdiff ---------------- 1 | 3 21 | 2 126 | 2 

So, I need the output to see something like:

 2 3 4 22 23 127 128 

I’m lost where to even start. Can anyone advise me on the SQL code for this solution?

Thanks!

Answer 1

 ;with a as ( select prevdoc + 1 col, exdiff from <table> where exdiff > 0 union all select col + 1, exdiff - 1 from a where exdiff > 1 ) select col 

Answer 2

If your exdiff is a small number, you can create a virtual table of numbers using SELECT..UNION ALL , as shown here, and join it:

 select prevdoc+number from doc join (select 1 number union all select 2 union all select 3 union all select 4 union all select 5) x on x.number <= doc.exdiff order by 1; 

I provided 5, but you can expand them as needed. You did not specify your DBMS, but in each of them there will be a source of sequential numbers, for example, in SQL Server, you can use:

 select prevdoc+number from doc join master..spt_values v on v.number <= doc.exdiff and v.number >= 1 and v.type = 'p' order by 1; 

The master..spt_values ​​table contains numbers from 0 to 2047 (when filtering by type = 'p').

Answer 3

If the numbers are not too large, you can use the following trick in most databases:

 select t.exdiff + seqnum from t join (select row_number() over (order by column_name) as seqnum from INFORMATION_SCHEMA.columns ) nums on t.exdiff <= seqnum 

The use of INFORMATION_SCHEMA columns in a subquery is arbitrary. The only goal is to generate a sequence of numbers, at least up to the maximum number of ecdiffels.

This approach will work in any database that supports ranking functions. Most databases have a way to generate a sequence (for example, recursie CTE in SQL Server and CONNECT BY in Oracle).