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
Click Kutools > Insert > Duplicate Rows / Columns based on cell value, see screenshot: 3. In the Copy and insert rows & columns dialog box, select Copy and insert rows option in the Type section, then select the data range you want to duplicate, and then specify the repeat time to duplicate the rows, see screenshot: 4.
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'
.
You could do that with a recursive CTE using UNION ALL
:
;WITH cte AS
(
SELECT * FROM Table1
UNION ALL
SELECT cte.[ID], cte.ProductFK, (cte.[Order] - 1) [Order], cte.Price
FROM cte INNER JOIN Table1 t
ON cte.[ID] = t.[ID]
WHERE cte.[Order] > 1
)
SELECT [ID], ProductFK, 1 [Order], Price
FROM cte
ORDER BY 1
Here's a working SQLFiddle.
Here's a longer explanation of this technique.
Since your input is too large for this recursion, you could use an auxillary table to have "many" dummy rows and then use SELECT TOP([Order])
for each input row (CROSS APPLY
):
;WITH E00(N) AS (SELECT 1 UNION ALL SELECT 1),
E02(N) AS (SELECT 1 FROM E00 a, E00 b),
E04(N) AS (SELECT 1 FROM E02 a, E02 b),
E08(N) AS (SELECT 1 FROM E04 a, E04 b),
E16(N) AS (SELECT 1 FROM E08 a, E08 b)
SELECT t.[ID], t.ProductFK, 1 [Order], t.Price
FROM Table1 t CROSS APPLY (
SELECT TOP(t.[Order]) N
FROM E16) ca
ORDER BY 1
(The auxillary table is borrowed from here, it allows up to 65536 rows per input row and can be extended if required)
Here's a working SQLFiddle.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With