SQL Generate a list of numbers between two columns grouped by ID-CodePudding

I have seen a few similar questions, but can't find one with this scenario. I need to generate a list of numbers between two columns grouped by ID.

create table #temp (id int, beg int, ending int)
insert into #temp values (1, 1, 5)
insert into #temp values (2, 2, 8)
insert into #temp values (3, 1, 3)

I need to end up with:

ID	Values
1	1
1	2
1	3
1	4
1	5
2	2
2	3
2	4
2	5
2	6
2	7
2	8
3	1
3	2
3	3

Any idea how I can do this?

CodePudding user response：

Using the Roman Pekar answer, considering we are using SQL Server.

with recursive Numbers as (
    select 0 as Number
    union all
    select Number   1
    from Numbers
    where Number < (select max(ending) from #temp)
)
select id, Number from #temp inner join Numbers where beg <= Number and ending >= Number;

CodePudding user response：

If you have a numbers table:

CREATE TABLE dbo.Numbers(n int PRIMARY KEY);

INSERT dbo.Numbers(n) SELECT rn FROM
(
  SELECT TOP (100000) rn = ROW_NUMBER() OVER (ORDER BY s1.name)
    FROM sys.all_objects AS s1
    CROSS JOIN sys.all_objects AS s2
    ORDER BY s1.name
) AS x;

Then you can do this with a simple join:

SELECT t.ID, [Values] = n.n
  FROM #temp AS t
  INNER JOIN dbo.Numbers AS n
  ON n.n BETWEEN t.beg AND t.ending;

Results:

ID	Values
1	1
1	2
1	3
1	4
1	5
2	2
2	3
2	4
2	5
2	6
2	7
2	8
3	1
3	2
3	3

Example db<>fiddle

CodePudding user response：

The easiest way to do this is with a 'numbers' table. A numbers table simply includes the numbers 1, 2, 3, 4 etc up to a large number - larger than you'll ever need. It may also include 0 if desired.

If you have a numbers table, the simple thing to do is to join your original #temp table with the numbers table using inequalities - e.g., find all numbers that are >= the beginning value, and <= the ending value.

Below is an example, where #Nums is a numbers table.

create table #temp (id int, beg int, ending int);
insert into #temp values (1, 1, 5), (2, 2, 8), (3, 1, 3);

SELECT      #temp.id, #Nums.n
    FROM    #temp
            INNER JOIN #Nums ON #temp.beg <= #Nums.n AND #temp.ending >= #Nums.n

@Andre's answer above uses a numbers table as well - however it is created recursively.

To create a numbers table efficiently, here is one of the better methods (using cross-joins) - this creates a table with 100,000 rows from 0 to 99,999, and is easily expanded.

CREATE TABLE #Nums (n int PRIMARY KEY);
INSERT INTO #Nums (n)
    SELECT  a.n   b.n * 10   c.n * 100   d.n * 1000   e.n * 10000
    FROM    (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9)) AS a(n)
            CROSS JOIN (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9)) AS b(n)
            CROSS JOIN (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9)) AS c(n)
            CROSS JOIN (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9)) AS d(n)
            CROSS JOIN (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9)) AS e(n);