0

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).

0
-1
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);
1
  • 2
    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).
    – Paul White
    Aug 29 '18 at 17:19
2

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:

plan

1

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.