Repeat row based on the number in a column

Question

SqlFiddle Demo

I need to repeat each barcode of the article based on the quantity of this article in the table Stock.

This is source data:

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

I want 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 use table of numbers. Either permanent, or generated on the fly.

Query below uses CTE to generate up to 1000 numbers. Here is 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 2

You can get this by 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 SQL Fiddle