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

Siyavash Hamdi · Answer 1 · 2018-04-24T05:13:17.270

42

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

edited Apr 24 '18 at 05:13

answered Jan 22 '17 at 10:40

Siyavash Hamdi

2,764
2
21
32

1

Works wonderful, simple and clear. Little edit **master.dbo.spt_values t2 on t2.type = 'P' and t2.number < t1.Quantity** – IncreMan Oct 26 '17 at 09:51
The problem here is that it just works for under 2048 records. :( – Barsham Nov 26 '18 at 23:18
@Barsham, You're right. but this is a simple answer for small domain. If you need more than 2048 records, you must use some other ways such as `Recursive CTE` or `CROSS JOIN` with `master.dbo.spt_values` in order to get larger values. – Siyavash Hamdi Nov 27 '18 at 06:14
Thanks @SiyavashHamdi for your comment. I end up using JOIN with an existing table with 1 million sequental records which was in my system and performance was more align with my requirements. – Barsham Nov 27 '18 at 08:21

score 13 · Accepted Answer · edited May 23 '17 at 12:18

13

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.

edited May 23 '17 at 12:18

Community

1
1

answered Oct 25 '15 at 09:01

Amit

45,440
9
78
110

I get this error: The maximum recursion 100 has been exhausted before statement completion – franchesco totti Oct 25 '15 at 09:06
what's the maximum `[Order]` value you have? – Amit Oct 25 '15 at 09:08
could implement with Cross Apply ? – franchesco totti Oct 25 '15 at 09:10
1

@franchescototti: Use [option maxrecursion](https://msdn.microsoft.com/en-AU/library/ms175972.aspx) – cha Oct 25 '15 at 10:08
@cha - `maxrecursion` won't help here. recursion end when it's no longer needed in the first solution. limiting to any lower bound won't result in the required output – Amit Oct 25 '15 at 11:40
@cha - ohhh... you mean `MAXRECURSION 0`... Yes, that's a valid option. – Amit Oct 25 '15 at 11:49
Is there a mysql solution to it? It looks like if we use MySQL the above method will not work. – June Jun 16 '21 at 19:29

score 0 · Answer 3 · edited Sep 01 '23 at 00:30

0

The generate_series keyword might be used:

Select ID,ProductFK,1 as Quantity, Price from TableA t cross join generate_series(1,t.Quantity);

edited Sep 01 '23 at 00:30

Jeremy Caney

7,102
69
48
77

answered Aug 31 '23 at 07:21

Soutik Kumar

1
1

As it’s currently written, your answer is unclear. Please [edit] to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers [in the help center](/help/how-to-answer). – Community Sep 01 '23 at 00:30

score -4 · Answer 4 · edited Dec 26 '17 at 07:39

-4

CREATE TAblE #temp
(
T_Name      VARCHAR(50),
T_Times      BIGINT
)

INSERT INTO #temp(T_Name,T_Times) VALUES ('ASHISH',4)
INSERT INTO #temp(T_Name,T_Times) VALUES ('PANKAJ',3)
INSERT INTO #temp(T_Name,T_Times) VALUES ('RUPESH',2)
INSERT INTO #temp(T_Name,T_Times) VALUES ('MANISH',5)

SELECT t.T_Name ,t.T_Times FROM
(SELECT  T_Name,T_Times,CAST(('<val>'+REPLICATE(T_Name+'</val><val>',T_Times-1)
+'</val>') AS XML )AS X FROM #temp)t CROSS APPLY t.X.nodes('/val')y(z)

drop table #temp

edited Dec 26 '17 at 07:39

user0042

7,917
3
24
39

answered Dec 26 '17 at 07:36

Ashish Srivastava

8
1

8

Keep your answers self contained please! Frequently referring to your blog site is close to spamming, and not really considered useful at Stack Overflow. – user0042 Dec 26 '17 at 07:41

Repeat Rows N Times According to Column Value

4 Answers4

Linked

Related