Select repeating number x times and then increment

I need a select query that can generate z amount of rows starting from 1, repeating x times and then increment to the next number.

For example 10 rows repeating 4 times would result in the following:

| # |
|---|
| 1 |
| 1 |
| 1 |
| 1 |
| 2 |
| 2 |
| 2 |
| 2 |
| 3 |
| 3 |

So z=10 and x=4 in the example given. These are parameters of a stored procedure. There are no limits or restrictions. The user can enter 2 billion if user needs 2 billion (unlikely).

DECLARE @z INT=10, @x INT=4;
WITH q AS (SELECT 0 AS n UNION ALL SELECT n+1 FROM q)
SELECT TOP(@z) n/@x+1 FROM q OPTION(MAXRECURSION 0);
• Technically, TOP needs an ORDER BY clause in the same scope to guarantee correct results. Recursive CTEs are not the most efficient way to generate numbers on the fly (not a particular concern if @z is small enough). Aug 29 '18 at 17:19

One solution using simple integer arithmetic on a table of numbers. This example uses an efficient way to generate a sequence on the fly, in practice you would probably have a permanent Numbers table.

DECLARE
@z bigint = 10,
@x integer = 4;

-- Itzik Ben-Gan's row generator
WITH
L0   AS (SELECT 1 AS c UNION ALL SELECT 1),
L1   AS (SELECT 1 AS c FROM L0 AS A CROSS JOIN L0 AS B),
L2   AS (SELECT 1 AS c FROM L1 AS A CROSS JOIN L1 AS B),
L3   AS (SELECT 1 AS c FROM L2 AS A CROSS JOIN L2 AS B),
L4   AS (SELECT 1 AS c FROM L3 AS A CROSS JOIN L3 AS B),
L5   AS (SELECT 1 AS c FROM L4 AS A CROSS JOIN L4 AS B),
Nums AS (SELECT ROW_NUMBER() OVER (ORDER BY CURRENT_TIMESTAMP) AS n FROM L5)
-- Main logic
SELECT TOP (@z)
# = ((N.n - 1) / @x) + 1
FROM Nums AS N
ORDER BY N.n;

Output:

╔═══╗
║ # ║
╠═══╣
║ 1 ║
║ 1 ║
║ 1 ║
║ 1 ║
║ 2 ║
║ 2 ║
║ 2 ║
║ 2 ║
║ 3 ║
║ 3 ║
╚═══╝

Execution plan: PostgreSQL

This can be easily done with PostgreSQL using generate_series

SELECT t.x
FROM generate_series(1,10) AS t(x)
CROSS JOIN LATERAL generate_series(1,4) AS g(x)
ORDER BY t.x;

Here we generate a series [1..10] and then repeat it four times.

Output

x
----
1
1
1
1
2
2
2
2
3
3
3
3