sql server - Print out same row multiple times based on calculated value -

i have query returns similar following:

zone    | neededitems =========================== 209     | 5 213     | 1 216     | 1 220     | 2 218     | 1 219     | 4 215     | 1 

the query behind like:

select     r.zone zone, r.required - count(i.item) neededitems     myitems inner join     myrequirements r on i.zone = r.zone group     r.zone, r.required 

where myitems looks like: (value of item doesn't matter)

zone    | item ================ 209     | 209     | b 209     | c 216     | 220     | 213     | z 218     | x 219     | q 219     | w 219     | e 219     | r 215     | t 

and myrequirements looks like:

zone    | required ====================== 209     | 8 213     | 2 216     | 2 220     | 3 218     | 2 219     | 5 215     | 2 

what need able print out zone multiple times based on value in needed. value in needed calculated value making difficult (i can't remove count!)

so results looking list of zones, each appearing number of times needed.

zone     ==== 209 209 209 209 209 213 216 220 220 218 219      219 219 219 215 

is there way in sql can done? using sql server 2012.

below one way - using e1, e2 , e3 queries not cleanest way it, it's way manage working.

one bit limitation: works 1000 items of each (more enough mine.) changed editing where c<9 aware recursive best not have more needed.

with cte (     select         r.zone zone, r.required - count(i.item) neededitems             myitems     inner join         myrequirements r on i.zone = r.zone     group         r.zone, r.required ), e1(n,c ) (     select 1, 0     union     select n, c + 1         e1         c<9 ), -- 10 e2(n) (     select 1 e1 cross join e1 b -- 100 ), e3(n) (     select 1 e1 cross join e2 -- 1000 ),  numbers (     select n = row_number() on (order n) e3 )  select     zone     numbers inner join     cte on cte.neededitems >= n order zone