Is it possible to generate rows within a table based on existing column in the table in SQL Server? For example, if RunDates value = 31/01/2020 and RunTimes = 3, then there should be 3 rows in the table for RunDate = 31/01/2020
Current table
Desired table
You can do it with a recursive CTE:
with cte as (
select RunDates, RunTimes, 1 nr from tablename
union all
select RunDates, RunTimes, nr + 1
from cte
where nr < RunTimes
)
select RunDates, RunTimes from cte
order by RunDates
See the demo.
Results:
> RunDates | RunTimes
> :--------- | -------:
> 2020-01-31 | 3
> 2020-01-31 | 3
> 2020-01-31 | 3
> 2020-02-29 | 2
> 2020-02-29 | 2
> 2020-03-31 | 1
First you need a tally table (view) e.g.
CREATE View [dbo].[cteTally]
as
WITH
E1(N) AS (select 1 from (values (1),(1),(1),(1),(1),(1),(1),(1),(1),(1))dt(n))
, E2(N) AS (SELECT 1 FROM E1 a, E1 b) --10E+2 or 100 rows
, E4(N) AS (SELECT 1 FROM E2 a, E2 b) --10E+4 or 10,000 rows max
, cteTally(N) AS (
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4
)
select N from cteTally
GO
Then you join it on to your table e.g.
declare @MyTable table (MyDate date, RunCount int);
insert into @MyTable (MyDate, RunCount)
values
('31 Jan 2020', 3),
('29 Feb 2020', 2),
('31 Mar 2020', 1);
select MT.*
from @MyTable MT
inner join cteTally T on T.N <= MT.RunCount
order by MyDate, RunCount;
Returns:
MyDate RunCount
--------------------
2020-01-31 3
2020-01-31 3
2020-01-31 3
2020-02-29 2
2020-02-29 2
2020-03-31 1
NOTE: The Tally Table is courtesy of @Larnu but I can't find the original post.
You can try the following inner join by creating a serial number from sys.objects.
Here I have fixed 10 assuming the maximum value of RunTime. You can create a variable and assign the maximum value of the RunTime value and use that variable in place of 10.
Here is an another way to do that.
create table SampleTable (DtDate Date, RunTimes int)
insert into SampleTable Values
('31 Jan 2020', 3),
('29 Feb 2020', 2),
('31 Mar 2020', 1)
SELECT SampleTable.*
FROM SampleTable
INNER JOIN (
SELECT TOP 10 ROW_NUMBER() OVER (
ORDER BY object_id
) AS SrNo
FROM sys.objects
) mst ON RunTimes >= SrNo
ORDER BY DtDate
Live db<>fiddle demo.
