# Recursive Cte

#1

Hi,
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

Thanks
Best Regards
Nicole

#2

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

``````SELECT
*
FROM
#tmp t
INNER JOIN #Numbers n
ON n.N <= row_count;``````

#3

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.

#4
``````;WITH
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
)
SELECT dt.*
FROM dbo.data_table dt
INNER JOIN cteTally10K t ON t.number BETWEEN 1 AND dt.RowCount``````