Repeat row based on number in column

Question

SqlFiddle Demo

I need to repeat each barcode of an article depending on the amount of that article in the Stock table.

This is the source data:

| BarCode | quantity |
|---------|----------|
| 5142589 |        7 |
|  123454 |        5 |
| 1111145 |        3 |

I need a result that looks like this:

Barcode
-------
5142589
5142589
5142589
5142589
5142589
5142589
5142589
123454
123454
123454
123454
123454
1111145
1111145
1111145

How can i do this?

thanks

Answer 1

You can get this with a simple recursive CTE .

 WITH cte
    AS
    (
      SELECT IdArticle,1 AS rn FROM TABLE_STOCK
      UNION ALL
      SELECT t.IdArticle,rn+1 AS rn
      FROM cte c 
        INNER JOIN TABLE_STOCK t ON t.IdArticle = c.IdArticle and rn<t.QUANTITY
    )

    SELECT t.BarCode,TS.QUANTITY
     FROM cte c 
        INNER JOIN TABLE_BARCODE t ON t.IdArticle = c.IdArticle 
        INNER JOIN TABLE_STOCK TS ON TS.IdArticle = C.IdArticle
    ORDER BY t.IdArticle

Here is the SQL Fiddle

Answer 2

You can use the table. Either permanent or generated on the fly.

The request below is used CTEto generate up to 1000 numbers. Here is the SQL Fiddle .

WITH 
e1(n) AS
(
    SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL 
    SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL 
    SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
) -- 10
,e2(n) AS (SELECT 1 FROM e1 CROSS JOIN e1 AS b) -- 10*10
,e3(n) AS (SELECT 1 FROM e1 CROSS JOIN e2) -- 10*100
,CTE_Numbers
AS
(
    SELECT ROW_NUMBER() OVER (ORDER BY n) AS Number
    FROM e3
)
SELECT b.BarCode, s.quantity
FROM 
  TABLE_BARCODE b
  INNER JOIN TABLE_STOCK s ON b.IdArticle = s.IdArticle
  CROSS APPLY
  (
    SELECT TOP(s.quantity) CTE_Numbers.Number
    FROM CTE_Numbers
    ORDER BY CTE_Numbers.Number      
  ) AS CA

Results:

| BarCode | quantity |
|---------|----------|
| 5142589 |        7 |
| 5142589 |        7 |
| 5142589 |        7 |
| 5142589 |        7 |
| 5142589 |        7 |
| 5142589 |        7 |
| 5142589 |        7 |
|  123454 |        5 |
|  123454 |        5 |
|  123454 |        5 |
|  123454 |        5 |
|  123454 |        5 |
| 1111145 |        3 |
| 1111145 |        3 |
| 1111145 |        3 |

Answer 3

:

DECLARE @t table(BarCode int, quantity int)
INSERT @t values(5142589, 7),(123454, 5),(1111145,3)

;WITH 
e1(n) AS
(
    SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL 
    SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL 
    SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
) -- 10
,e2(n) AS (SELECT 1 FROM e1 CROSS JOIN e1 AS b) -- 10*10
,e3(n) AS (SELECT 1 FROM e2 CROSS JOIN e2 ex) -- 100*100
SELECT BarCode
FROM @t t
CROSS APPLY
(
  SELECT top(t.quantity) null dummy 
  FROM e3
) x