Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Repeat Rows N Times According to Column Value

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
like image 911
franchesco totti Avatar asked Oct 25 '15 08:10

franchesco totti


People also ask

How do you duplicate rows and times?

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.


2 Answers

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'.

like image 124
Siyavash Hamdi Avatar answered Sep 19 '22 13:09

Siyavash Hamdi


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.

like image 41
Amit Avatar answered Sep 21 '22 13:09

Amit