2

I'm stuck on this SQL problem.

I have a column that is a list of starting points (prevdoc), and anther column that lists how many sequential numbers I need after the starting point (exdiff).

For example, here are the first several rows:

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

So I need an output to look something like:

2
3
4
22
23
127
128

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

Thanks!

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
apd2zn
  • 33
  • 4
  • 1
    if 1 is a starting point, why does you output start at 2? – EvilTeach Oct 06 '12 at 22:43
  • Nice question. I'm very much interested what people can come up with. SQL operates on sets (i.e. set of integers), so one would assume that there is a simpler solution than what Gordon proposed. – Robert Co Oct 07 '12 at 16:22
  • @EvilTeach: I think this bit answers your question: *‘column that lists how many sequential numbers I need **after** the starting point (`exdiff`)’* (emphasis is mine). – Andriy M Oct 07 '12 at 21:39

3 Answers3

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
t-clausen.dk
  • 43,517
  • 12
  • 59
  • 92
1

If your exdiff is going to be a small number, you can make up a virtual table of numbers using SELECT..UNION ALL as shown here and join to 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 have provided for 5 but you can expand as required. You haven't specified your DBMS, but in each one there will be a source of sequential numbers, for example in SQL Server, you could 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 between 0-2047 (when filtered by type='p').

RichardTheKiwi
  • 105,798
  • 26
  • 196
  • 262
0

If the numbers are not too large, then 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 the subquery is arbitrary. The only purpose is to generate a sequence of numbers at least as long as the maximum exdiff number.

This approach will work in any database that supports the ranking functions. Most databases have a database-specific way of generating a sequence (such as recursie CTEs in SQL Server and CONNECT BY in Oracle).

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • You can't refer to prevdoc in the subselect since the subselect can't detect the table c. cross apply seems messy here. So i surgest refering to new_id() or one of the existing columns. – t-clausen.dk Oct 06 '12 at 19:35
  • *"Most databases have a database-specific way of generating a sequence"* Also the cryptically named `generate_series()` function in PostgreSQL. – Mike Sherrill 'Cat Recall' Oct 07 '12 at 18:16
  • I'm sorry, I know you say it is arbitrary, but I'm confused about information_schema.columns. What is that, and how do I use it? – apd2zn Oct 08 '12 at 16:00