SQL Server repeat rows for n number of times

To repeat each row or record n number of times, based on some column value in a table, we could do it in two ways either by using CTE or if n is less than 2048 then you can do a simple inner join on master.dbo.spt_values table with a condition where column type = 'P' and check condition on where clause to check the number of rows replicated.

create table test_one(
    id int identity(1,1) primary key,
    name varchar(50),
    quantity int,
    active bit not null default(1)
);

insert into test_one(name, quantity) values ('product 1', 105), ('product 2', 77);

select * from test_one; -- returns all inserted rows

-- cte query to repeat rows for n number of times.
with test_cte as (
    select id, name, quantity as current_count, 1 as order_count from test_one

    union all

    select
        t2.id, t2.name, (t2.current_count - 1) as current_count, 1 as order_count
    from test_one t1
    join test_cte t2
        on t1.id = t2.id
    where t2.current_count > 1
)

select * from test_cte option (maxrecursion 0);

In the above test_cte in the anchor part, we had done a simple select query to retrieve rows, and along with it, we add order_count as value 1. It will be always 1 and repeat as many times as the value in current_count i.e. table's quantity is greater than 1.' In the second part of the CTE query after union all, we perform an inner join on the test_one table with test_cte, wherein we check that the test_cte's current_count is greater than 1 since we are decrementing the current_count by 1 each time. This will repeat rows for the given n number of times.