skilly2 (6/29/2015)

---

This solution worked as well. It works well with a small number of rows, but I don't think is easy as the above solution to adopt to a bigger number of rows (although my question specifically didn't say I'd ever have to adopt to more rows). I did learn something from the syntax though with the Tally(N) that I wasn't familiar with.

Actually there's nothing that would keep it working with any number of rows. The following is a variation that will produce a 1M row tally table.

WITH n (n) AS (

SELECT 1 FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) n (n)

), Tally (n) AS (

SELECT -- add a TOP N clause here, to limit the number of rows produced.

ROW_NUMBER() OVER (ORDER BY (SELECT NULL))

FROM n n1, n n2, n n3, n n4, n n5, n n6

)

SELECT n FROM Tally