How to expand rows from count in tsql only

Question

I have a table that contains a number and a range value. For instance, one column has the value of 40 and the other column has a value of 100 meaning that starting 40 the range has 100 values ending in 139 inclusive of the number 40. I want to write a tsql statement that expands my data into individual rows.

I think I need a cte for this but do not know how I can achieve this.

Note: when expanded I am expecting 7m rows.

Answer 1

you could try this approach:

create function [dbo].[fRange](@a int, @b int)
    returns @ret table (val int)
as 
begin
    declare @val int
    declare @end int
    set @val = @a
    set @end = @a + @b
    while @val < @end
    begin
     insert into @ret(val)
     select @val
     set @val = @val+1
    end
return
end

go

declare @ranges table(start int, noOfEntries int)

insert into @ranges (start, noOfEntries)
select 40,100
union all select 150, 10

select * from @ranges r
    cross apply dbo.fRange(start,noOfEntries ) fr

not the fastest but should work

Answer 2

I don't know how this could be done with common table expressions, but here is a solution using a temporary table:

   SET NOCOUNT ON

   DECLARE @MaxValue INT
   SELECT @MaxValue = max(StartValue + RangeValue) FROM MyTable

   DECLARE @Numbers table ( 
      Number INT IDENTITY(1,1) PRIMARY KEY 
   )

   INSERT @Numbers DEFAULT VALUES 

   WHILE COALESCE(SCOPE_IDENTITY(), 0) <= @MaxValue 
      INSERT @Numbers DEFAULT VALUES 

    SELECT n.Number
    FROM   @Numbers n
    WHERE  EXISTS(
        SELECT *
        FROM   MyTable t
        WHERE  n.Number BETWEEN t.StartValue AND t.StartValue + t.RangeValue - 1
    )

    SET NOCOUNT OFF

Could be optimized if the Numbers table was a regular table. So you don't have to fill the temporary table on every call.

Answer 3

I would do something slightly different from splattne...

SET NOCOUNT ON

DECLARE @MaxValue INT
DECLARE @Numbers table (
    Number INT IDENTITY(1,1) PRIMARY KEY CLUSTERED
 )

SELECT @MaxValue = max(RangeValue) FROM MyTable
INSERT @Numbers DEFAULT VALUES

WHILE COALESCE(SCOPE_IDENTITY(), 0) <= @MaxValue
    INSERT @Numbers DEFAULT VALUES

SELECT
    t.startValue + n.Number
FROM
    MyTable t
INNER JOIN
    @Numbers n
        ON n.Number < t.RangeValue

SET NOCOUNT OFF

This will minimise the number of rows you need to insert into the table variable, then use a join to 'multiply' one table by the other...

By the nature of the query, the source table table doesn't need indexing, but the "numbers" table should have an index (or primary key). Clustered Indexes refer to how they're stored on the Disk, so I can't see CLUSTERED being relevant here, but I left it in as I just copied from Splattne.

(Large joins like this may be slow, but still much faster than millions of inserts.)

Answer 4

If you want CTE here is an example:

Initial insert:

insert into rangeTable (StartValue, RangeValue)
select 40,100
union all select 150,10
go

the query:

with r_CTE (startVal, rangeVal, generatedVal)
as
(
    select r.startValue, r.rangeValue, r.startValue
    from rangeTable r
    union all
    select r.startValue, r.rangeValue, generatedVal+1
    from rangeTable r
    inner join r_CTE rc 
     on r.startValue = rc.startVal
     and r.rangeValue = rc.rangeVal
     and r.startValue +  r.rangeValue > rc.generatedVal + 1
)
select * from r_CTE
order by startVal, rangeVal, generatedVal

Just be aware that the default maximum number of recursions is 100. You can change it to the maximum of 32767 by calling

option (maxrecursion 32767)

or to no limit

option (maxrecursion 0)

See BOL for details