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
;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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With