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 |
7
  • Where are z and x defined? Can you show an example that's a little more real? Aug 29 '18 at 17:08
  • Z=10 and X=4 from the example given.
    – lizzy91
    Aug 29 '18 at 17:18
  • Yes, I read the text, I was asking where SQL Server will get them. Parameters? Local variables? A table? A query? A function? Aug 29 '18 at 17:21
  • Paramaters of a stored proc
    – lizzy91
    Aug 29 '18 at 17:21
  • 1
    I'm trying to help you write better requirements, nothing more. Aug 29 '18 at 17:43
-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
1

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

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.