How Can I Generate Rows In A SQL Table To Break Up A Range Into Induvidual Rows

I have data with info similar to the below: Customer Start End AAA 100 399 BBB 400 899 CCC 900 999 AAA 1000 1199 What I need it to bec

Solution 1:

Best approach I would recommend is to go for recursive with CTE. Please find the following code snippet to do it.

declare @source table (customer varchar(15), rangeStart int, rangeEnd int)

insert into @source (customer, rangeStart, rangeEnd)
select 'AAA', 100, 399
union
select 'BBB', 400, 899
union
select 'CCC', 900, 999
union
select 'AAA', 1000, 1199


select * from @source

; with results as (
    select Customer, rangeStart, rangeEnd, rangeNumber = rangeStart
        from @source
    union all
    select Customer, rangeStart, rangeEnd, rangeNumber + 1
        from results
        where rangeNumber <= rangeEnd

)

select * 
    from results 
    order by rangeNumber
    option (MAXRECURSION 20000)

Solution 2:

If you already have a numbers table, use it. If not, you can generate a numbers table on the fly using a cte.

First, create and populate sample table(Please save us this step in your future questions):

DECLARE @T AS TABLE
(
    Customer char(3),
    Start int,
    [End] int
);

INSERT INTO @T(Customer, Start, [End]) VALUES
('AAA', 100, 399), 
('BBB', 400, 899),
('CCC', 900, 999),  
('AAA', 1000, 1199);

Then, use a cte to generate the numbers you need

WITH Tally(n) AS
(
    SELECT TOP(select max([End]) from @T) ROW_NUMBER() OVER(ORDER BY @@SPID) 
    FROM sys.objects
)

Finally, select from the table joined to the cte:

SELECT Customer, n
FROM @T
JOIN Tally 
    ON n >= Start
    AND n <= [End]
ORDER BY Customer, n