Recursive cte to repeat multiple integers
I need a column of numbers: Seven integer 1 events followed by 7 2 cases followed by 7 3… cases followed by 7 n-1 cases followed by 7 n cases. Thus
Num
1
1
1
1
1
1
1
2
2
2
2
2
2
2
...
...
n-1
n-1
n-1
n-1
n-1
n-1
n-1
n
n
n
n
n
n
n
Unfortunately, I haven't gotten too far. My current attempt is as follows, where n = 4:
WITH
one AS
(
SELECT num = 1,
cnt = 0
UNION ALL
SELECT num = num,
cnt = cnt + 1
FROM one
WHERE cnt < 7
),
x AS
(
SELECT num,
cnt = 0
FROM one
UNION ALL
SELECT num = num + 1,
cnt = cnt + 1
FROM one
WHERE cnt < 4
)
SELECT *
FROM x
source to share
No need to use recursive CTE
, for that you can try to install a solution based approach, try something like this. View integer
division.
If you have access to the main database use this.
;with cte as
(
SELECT top 1000 [7_seq] = ( ( Row_number()OVER(ORDER BY (SELECT NULL)) - 1 ) / 7 ) + 1
FROM sys.columns
)
select * from cte where [7_seq] <= @n
or use tally table
to generate numbers. I will prefer this solution
DECLARE @n INT = 10;
WITH Tally (num)
AS (
-- 1000 rows
SELECT Row_number()OVER (ORDER BY (SELECT NULL))
FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) a(n)
CROSS JOIN (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) b(n)
CROSS JOIN (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) c(n)),
seq
AS (SELECT [7_seq] = ( ( Row_number()
OVER(
ORDER BY (SELECT num)) - 1 ) / 7 ) + 1
FROM Tally)
SELECT [7_seq]
FROM seq
WHERE [7_seq] <= @n
source to share
You can do this with the following:
DECLARE @num INT = 1,
@sub INT = 0,
@max INT = 10,
@timesToRepeat INT = 7
CREATE TABLE #Temp (num INT)
WHILE (@num < @max + 1)
BEGIN
SET @sub = 0;
WHILE (@sub < @timesToRepeat)
BEGIN
INSERT INTO #Temp
SELECT @num x
SET @sub = @sub +1
END
SET @num = @num +1
END
SELECT * FROM #Temp
DROP TABLE #Temp
Set the variable @max
to set what you want to achieve. It is currently 10, so it will return a result like:
1 1 1 1 1 1 1 2 2 2 2 2 2 2 . . . 10 10 10 10 10 10 10
source to share
WITH t1 AS (SELECT 0 as num UNION ALL SELECT 0)
,t2 AS (SELECT 0 as num FROM t1 as a CROSS JOIN t1 as b)
,t3 AS (SELECT 0 as num FROM t2 as a CROSS JOIN t2 as b)
,t4 AS (SELECT 0 as num FROM t3 as a CROSS JOIN t3 as b)
,Tally (number)
AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 1)) FROM t4)
SELECT t1.number
FROM Tally as t1 cross join Tally as t2
where t2.number <=7
ORDER BY t1.number;
source to share
DECLARE @MAX INTEGER
SET @MAX = 5;
with cte as
(SELECT 7 as num
UNION ALL
SELECT num-1 as num from cte where num>1
),cte2 AS
(SELECT @MAX as num
UNION ALL
SELECT num-1 as num from cte2 where num>1)
select C2.num from cte C1,cte2 C2 ORDER by C2.num asc
Change the @MAX value to display the value n
source to share
Here's a slightly different way to do it.
select null num into #a
union all
select null
union all
select null
union all
select null
union all
select null
union all
select null
union all
select null
select * into #b from
(select rn = row_number()over (order by (select null)) from sys.objects A cross join sys.objects B) A
where rn <=10
select #b.rn as numbers from #a cross join #b
order by 1
source to share