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