Multiply rows in single query

Question

Table1 has the following 2 columns and 4 rows:

Entity Number
------ ------
Car    4
Shop   1
Apple  3
Pear   1

I'd like to have one set based SQL query, which produces the below desired results. Basically duplicating the Entities by the Number of times in the Number column.

I could only do it by loop through the rows one by one, which is not really elegant, neither set based.

Desired result:

Entity
------
Car   
Car   
Car   
Car   
Shop  
Apple 
Apple 
Apple 
Pear  

Answer 1

One method uses recursive CTEs:

with cte as (
      select t1.entity, t1.number
      from table1 t1
      union all
      select cte.entity, cte.number - 1
      from cte
      where cte.number > 0
     )
select entity
from cte;

Note: Using the default settings, this is limited to 100 rows per entity. You can use OPTION (MAXRECURSION 0) to get around this.

You can also solve this with a numbers table, but such a problem is a good introduction to recursive CTEs.