SQLTeam.com | Weblogs | Forums

Recursive Cte


i have a Select with this Result;

ID RowCount
FKR000000000001 34
FKR000000000002 21
FKR000000000003 28

Can i write a recursive Cte, which generates as many records as the RowCount indicates.
For example: (FKR000000000001 34) = > generates 34 same Records

Best Regards


You could use recursive query, but it is easier to use a numbers table.

	#tmp t
	INNER JOIN #Numbers n
		ON n.N <= row_count;


I wanted to post the code to create a numbers table, but the site keeps giving me an error. Perhaps someone else can post, or you can search the web.

cteTally10 AS (
    SELECT * FROM (VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) AS Numbers(Number)
cteTally100 AS (
    SELECT 0 AS number
    FROM cteTally10 c1
    CROSS JOIN cteTally10 c2
cteTally10K AS (
    SELECT ROW_NUMBER() OVER(ORDER BY c1.number) AS number
    FROM cteTally100 c1
    CROSS JOIN cteTally100 c2
FROM dbo.data_table dt
INNER JOIN cteTally10K t ON t.number BETWEEN 1 AND dt.RowCount