Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Recursive cte to repeat several integers

I'd like a column of numbers: Seven occurances of the integer 1, followed by 7 occurances of 2, followed by 7 occurances of 3 .... , followed by 7 occurances of n-1, followed by 7 occurances of n. Like so

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've not progressed too far. My current attempt is the following, 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
like image 310
whytheq Avatar asked Jan 28 '26 05:01

whytheq


1 Answers

;WITH Numbers AS
(
    SELECT n = 1
    UNION ALL
    SELECT n + 1
    FROM Numbers
    WHERE n+1 <= 10
),
se7en AS
(
    SELECT n = 1
    UNION ALL
    SELECT n + 1
    FROM se7en 
    WHERE n+1 <= 7
)
SELECT Numbers.n
FROM Numbers CROSS JOIN se7en
like image 197
Fruitbat Avatar answered Jan 29 '26 22:01

Fruitbat