sql - Repeat Rows N Times According to Column Value

Question

I have following table.

Table A:
ID         ProductFK         Quantity       Price
------------------------------------------------
10         1                  2           100
11         2                  3           150
12         1                  1           120
----------------------------------------------

I need select that repeat Rows N Time According to Quantity Column Value.

So I need following select result:

ID        ProductFK         Quantity        Price
------------------------------------------------
10        1                   1          100
10        1                   1          100
11        2                   1          150
11        2                   1          150
11        2                   1          150
12        1                   1          120

Answer 1

You can use a simple JOIN to get the desired result as below:

SELECT  t1.*, t2.number + 1 RepeatNumber
FROM    TableA t1
JOIN    master.dbo.spt_values t2 ON t2.type = 'P' AND t2.number < t1.Quantity

The above query repeats each record by the specified number in Quantity column.

Note for master.dbo.spt_values on type = 'P':
This table is used for getting a series of number which is hard-coded in it by
condition of type = 'P'.