Tally table in SQL

I had a situation where I had to write out to a file where the  column3 ( string)  should be split and write to the next line if the length was greater than 50 . I used Tally table to do that.

WITH cteTally AS
(
SELECT *
FROM (VALUES(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) t(v)
)

,cte AS
(
SELECT
  v = (ROW_NUMBER() OVER(ORDER BY c1.v) * 50  + 1) - 50
FROM cteTally c1
CROSS JOIN cteTally c2
)

SELECT
  tbl1.col1   'Column1'
  ,tbl1.col2  'Column2'
  ,SUBSTRING(rtrim(ltrim(tbl1.Column3)),c.v,50)  'Column3'
FROM tbl1
CROSS JOIN cte c
WHERE
SUBSTRING(rtrim(ltrim(tbl1.Column3)),c.v,50) <> ''

Explanation ---

select * from cteTally
V
-----
1
1
1
1
1
1
1
1
1
1
Select * from cte
v
----
1
51
101
151
201
.
.
.
.
4951
(100 rows because of cross join)
finally use the place holder row value in the sub string

SELECT
  tbl1.col1   'Column1'
  ,tbl1.col2  'Column2'
  ,SUBSTRING(rtrim(ltrim(tbl1.Column3)),c.v,50)  'Column3'
FROM tbl1
CROSS JOIN cte c
WHERE
SUBSTRING(rtrim(ltrim(tbl1.Column3)),c.v,50) <> ''

Another easy to understand example that I found on http://blogs.inkeysolutions.com/2011/05/creating-tally-tables-using-cte-in-sql.html
Generating a sequence of numbers from 1 to 20
DECLARE @Max AS INT = 20


;WITH CTE AS (
  SELECT 1 Num
  UNION ALL
  SELECT Num + 1 FROM CTE WHERE Num < @Max
)


SELECT * FROM CTE
 



----Generating a sequence of Dates starting with the current date & going till next 20 days


DECLARE @MaxDate AS DATETIME = GETDATE() + 20


;WITH CTE AS (
  SELECT GETDATE() Dates
  UNION ALL
  SELECT Dates + 1 FROM CTE WHERE Dates < @MaxDate
)


SELECT * FROM CTE

---Generating a sequence of Dates starting with the current date & going till next 20 days


DECLARE @MaxDate AS DATETIME = GETDATE() + 20


;WITH CTE AS (
  SELECT GETDATE() Dates
  UNION ALL
  SELECT Dates + 1 FROM CTE WHERE Dates < @MaxDate
)


SELECT * FROM CTE