(Recursive) SQL query instead of loops

Question

I'm looking for a query to get the following output:

Id Number
-- ------
 1 241100
 2 241110
 2 241111
 2 241112
 2 241113
 2 241114
 2 241115

Table strucutre:

Id Number From To
-- ------ ---- ----
 1 241100 NULL NULL
 2 241110  111  115

Rows without a from/to range has to return the number. The other ones have to return the number followed by SUBSTRING(Number, 1, 3) + <from/to range>

One possible solution would be using while-loops. But thats not the way I'd prefer. And it's quite slow. And there's no way to change the data structure. We query data from a third party supplier.

---

On application site I've a (very small) list of numbers such as '241113', '241000', ... and need to know to which id this number is assigned to.

The query I will use to get a result is:

SELECT Id, Number FROM MyView WHERE Number IN ('241113', '241000')

Answer 1

You can create an auxiliary numbers table

CREATE TABLE Numbers
(
N int primary key
)


  WITH E00(N) AS (SELECT 1 UNION ALL SELECT 1),
        E02(N) AS (SELECT 1 FROM E00 a, E00 b),
        E04(N) AS (SELECT 1 FROM E02 a, E02 b),
        E08(N) AS (SELECT 1 FROM E04 a, E04 b),
        E16(N) AS (SELECT 1 FROM E08 a, E08 b),
        E32(N) AS (SELECT 1 FROM E16 a, E16 b),
   cteTally(N) AS (SELECT ROW_NUMBER() OVER (ORDER BY N) FROM E32)

   INSERT INTO Numbers
   SELECT N FROM cteTally
  WHERE N <= 1000000;

then to generate your desired results

;WITH T (Id, Number, [From], [To]) AS
(
 SELECT 1, 241100, NULL, NULL UNION ALL
 SELECT 2, 241110,  111,  115
)

SELECT Id, Number + N-1  AS Number
FROM T
JOIN Numbers ON N <= 1 + ISNULL(1 + [To] - [From],0)

Answer 2

The query I will use to get a result is:

SELECT Id, Number FROM MyView WHERE Number IN ('241113', '241000')

This is how you can write that query instead. There is no need to generate the numbers.

declare @T table
(
  Id int,
  Number int,
  [From] int,
  [To] int
)

insert into @T values
(1, 241100, NULL, NULL),
(2, 241110,  111,  115)

select T.Id, V.Number
from @T as T
  inner join (values (241113), 
                     (241100)) as V(Number)
    on V.Number between T.Number and T.Number + isnull(T.[To], 0)

A version where you put the numbers you look for in a table variable instead.

declare @V table(Number int)
insert into @V values(241100)
insert into @V values(241113)

select T.Id, V.Number
from @T as T
  inner join @V as V
    on V.Number between T.Number and T.Number + isnull(T.[To], 0)

I have not used From anywhere because it is unclear to me what values is possible in that column other then null and number+1.

And a version where you generate the numbers before filtering them out. The result is the same and I believe performance is not as good.

;with C as
(
  select T.Id,
         T.Number
  from @T as T
  union all
  select T.Id,
         C.Number + 1
  from @T as T
    inner join C
      on C.Id = T.Id
  where stuff(C.Number, 1, 3, '') < T.[To]
)
select Id, Number
from C
where Number in ('241113', '241100')

Answer 3

I know that you're using tsql, but out of curiosity I wanted to see how I could hack together a solution in pgsql:

create schema arrays;
set search_path = 'arrays';

create table ranges
(
    "Id"     bigint primary key,
    "Number" int not null,
    "From"   int,
    "To"     int
);

insert into ranges("Id", "Number", "From", "To") values
    (1,  241100, null, null),
    (2,  241110,  111,  115),
    (3, 2411200, 1281, 1293);

create view ranges_gen as
select
    "Id",
    (row_number() over(partition by "Id") - 1 + "Bottom") as "Number"
from
(
    select
        "Id",
        coalesce(round("Number", -length("From"::text)) + "From", "Number") as "Bottom",
        unnest(array_fill(0, array[coalesce("To" - "From" + 1, 1)]))
    from ranges
) as ranges_duped;

select * from ranges_gen;