Duplicate rows in SELECT statement

Question

I'm trying my best to avoid using cursors- the amount of data is really huge. There is a table that looks like:

|Data| Multiplier|
-----------------|
|A   | 2         |
|B   | 3         |
|C   | 0         |

I need to get data from it in the following way:

|Data| Multiplier|
-----------------|
|A   | 2         |
|A   | 2         |
|B   | 3         |
|B   | 3         |
|B   | 3         |

So that the row appears as much times, as it's "Multiplier" value. I know it's possible to use CONNECT statements in Oracle Database, but I need to do it in MSSQL.

Answer 1

You need recursive way :

with t as (
     select data, Multiplier, 1 as seq
     from table
     where Multiplier > 0
     union all
     select data, Multiplier, seq+1
     from t
     where seq < Multiplier
)
select *
from t
option (maxrecursion 0);