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 |
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: